Hi @k26_23 , this is your data set A. I have not figure out the solution for your request, also I do not quiet understand the intention of your request, i.e., what kind of dataset you would like to create, and what's for?   It looks to me like you are working on a dataset that has link ID to other datasets. But what's the sort-order column for? Looking from your dataset A, this column is character (if it is numeric the '01' to '06' would be displayed like '1' to '6', without the leading 0), so what's this column for (I converted this column to numeric, incase it is needed as an order variable)?   And it looks to me like, by the sort-order column you created a number for each Parameter column value, if so why not just order your data according to the Parameter column? Are you prompting some convoluted question and a false request that confused yourself? I tried some sql step but did not get the dataset you request. I'll post it here also. data tumor1; input Subject $1-7 Site 9-11 ID $13-17 Description $32. Parameter $51-61 ParamDesc $43. sortorder $12.; sortordernum=input(sortorder,8.); datalines; 101-276 101 NTLDAT Date of Assessment 01 101-276 101 NTL01 CARCINOMATOSIS:CARCINOMATOSIS NTLSTAT Status 02 101-276 101 NTL02 LIVER:LIVER LESIONS NTLSTAT Statu 02 101-276 101 TLDAT Date of Assessment 03 101-276 101 TL01 ABDOMEN:RUQ OMENTAL MET TLMEAS_C Measurement 04 101-276 101 TL02 ABDOMEN:LEFT ABDOMEN OMENTAL MET TLMEAS_C Measurement 04 101-276 101 TL03 LIVER:LEFT LIVER DOME TLMEAS_C Measurement 04 101-276 101 TL04 LIVER:RIGHT LIVER DONE TLMEAS_C Measurement 04 101-276 101 TL01 ABDOMEN:RUQ OMENTAL MET TLYN1 Was lesion assessed at this visit 05 101-276 101 TL02 ABDOMEN:LEFT ABDOMEN OMENTAL MET TLYN1 Was lesion assessed at this visit 05 101-276 101 TL03 LIVER:LEFT LIVER DOME TLYN1 Was lesion assessed at this visit 05 101-276 101 TL04 LIVER:RIGHT LIVER DONE TLYN1 Was lesion assessed at this visit 05 101-276 101 TL01 ABDOMEN:RUQ OMENTAL MET TLYN2 Check if lesion is too small to measure 06 101-276 101 TL02 ABDOMEN:LEFT ABDOMEN OMENTAL MET TLYN2 Check if lesion is too small to measure 06 101-276 101 TL03 LIVER:LEFT LIVER DOME TLYN2 Check if lesion is too small to measure 06 101-276 101 TL04 LIVER:RIGHT LIVER DONE TLYN2 Check if lesion is too small to measure 06 101-276 101 TLSUM_C Sum of Diameters 07 101-276 101 NLDAT Date of Assessment 08 101-276 101 NL01 LIVER:RIGHT LOBE LIVER LESION NLSTAT Status 09 101-276 101 NL02 LIVER:LIVER LESIONS NLSTAT Status 09 101-276 101 NL01 LIVER:RIGHT LOBE LIVER LESION NLMEAS Measurement 10 101-276 101 NL02 LIVER:LIVER LESIONS NLMEAS Measurement 10 101-276 101 RSDAT Date of Assessment 11 101-276 101 RSORRES1 RECIST 1.1 Target Response 12 101-276 101 RSORRES2 RECIST 1.1 Non-Target Response 13 101-276 101 RSYN1 Were unequivocal new lesions identified 14 101-276 101 RSORRES4 Tumor Biomarker Response 15 101-276 101 TLPCNTCH % Change from Baseline 16 101-276 101 TLNADIR % Change from NADIR 17 101-276 101 RSORRES3 RECIST 1.1 Overall Response 18 ; run; proc print data=tumor1;run; The sql step I tried: (it does not create the request dataset though, just add a hint to solve the question): proc sql; select * from tumor1 group by sortordernum order by sortordernum,id; quit; ... View more

Hi k26_23, What dataset is this ? What is NT, NL, and TL? Are they domain name or specific column (eg., like link id) values? ... View more

Sorry I made several mistakes in the code in my last thread 😂and corrected them here. The code should be like this. But I still have not wrote the step for combine the three  str_address&i datasets together😂, hi @SunnySong , have you finished writing your macro, please kindly let me know, thanks 😀 data street_name; input st_nm $ typ 4.; datalines; ROAD 1 RD 1 STREET 1 ST 1 PLACE 1 PL 1 PO_BOX 3 UNIT 2 SUITE 2 ; run; proc print data=street_name;run; data address; input address $20.; datalines; 3 ARRON ROAD 15 BONNY RD 7 DAISY STREET PO BOX 15 UNIT 6 89 ROSE PLACE 9 HOPE ST ; run; proc print data=address;run; %macro addressloc; proc sql; select distinct typ into :typlist1- from street_name; quit; %put &typlist1 &sqlobs; %let n=&sqlobs; %put &n; %do i=1 %to &n; proc sql; select st_nm into :strlist separated by '|' from street_name where typ=&&typlist&i; quit; %put &strlist; data str_address&i; set address; loc=prxmatch("/(&strlist)/",address); if loc^=0 then found=&&typlist&i; run; proc print data=str_address&i;run; %end; %mend addressloc; %addressloc; ROAD|RD|STREET|ST|PLACE|PL NOTE: There were 7 observations read from the data set WORK.ADDRESS. NOTE: The data set WORK.STR_ADDRESS1 has 7 observations and 3 variables. UNIT|SUITE NOTE: There were 7 observations read from the data set WORK.ADDRESS. NOTE: The data set WORK.STR_ADDRESS2 has 7 observations and 3 variables. PO_BOX NOTE: There were 7 observations read from the data set WORK.ADDRESS. NOTE: The data set WORK.STR_ADDRESS3 has 7 observations and 3 variables.         ... View more

Hi @SunnySong , I think this is the macro you are going to write. "PO_BOX" in the first dataset became "PO BOX" in the second dataset, so the value of the new column in the final output table is missing (it would be "3" if the "PO_BOX" value was the same in the first and second dataset). If you want to deal with this issue (the value in the second dataset is different from those in the first dataset) you need more coding step. You do not need any parameters for the macro. The macro can be simplified a bit because what you want is just the last output table. When finalize the macro do not forget to add the %local statement. data street_name; input st_nm $ typ 4.; datalines; ROAD 1 RD 1 STREET 1 ST 1 PLACE 1 PL 1 PO_BOX 3 UNIT 2 SUITE 2 ; run; proc print data=street_name;run; data address; input address $20.; datalines; 3 ARRON ROAD 15 BONNY RD 7 DAISY STREET PO BOX 15 UNIT 6 89 ROSE PLACE 9 HOPE ST ; run; proc print data=address;run; %macro addressloc; proc sql; select distinct typ into :typlist from street_name; quit; %put &typlist &sqlobs; %let n=&sqlobs; %put &n; %do i=1 %to &n; proc sql; select st_nm into :strlist separated by '|' from street_name where typ=&i; quit; %put &strlist; data str_address; set address; set str_address; loc=prxmatch("/(&strlist)/",address); if loc^=0 then found=&i; run; proc print data=str_address;run; %end; %mend addressloc; %addressloc;         ... View more

Hi @SunnySong , thank you for the feedback! I am glad that I can contribute and help a bit 😀 and best luck in testing your code (the macro you are going to write)! ... View more

Hi @Tom , I think you are so brilliant at find out the major issue in answering this question, which is to add a column to be used as the group variable in proc report step, and you solved the issue very effectively and promptly, I cannot find another way better than use the n(of y:)=0; statement, so I write my version based on your ideas and this code.   However, what I would like to tell you is that, this major issue(create a group variable to be used in the proc report step and meanwhile consider the order to be displayed in the final report) perhaps is not THAT substantial and need THAT much thinking and THAT many steps/syntax to solve it (because I see in your code a lot of statements were used to deal with this issue and resulted redundant coding). Super good logic/math thinking and super good at/or remember a lot of syntax somehow becomes disadvantage here I guess😀. And what you wrote in this part (a big portion of this part) maybe not necessary (see screen capture):     As for the order of the group variable in the proc report step, the simplest way to solve it is use the order=; option. Here use order=data; makes the data to be displayed in the original order. While writing the proc report; step, I referred to SAS base 9.4 guide and see the order=; options in the 1st (or 2nd example) and I used that option (when I write proc report I always need to refer to the examples in the guide, among SAS base procs, proc report and proc tabulate probably are the steps I am least familiar with and practiced least somehow😀). ... View more

Sorry, I did several minor changes in the code while posting and editing my last thread and made a mistake in the "order=data" option (I somehow mistakenly deleted the "=data" part and the order became alphabetic, not the original order in the dataset). So the final-final version of the code is as follows (the output and results are the same with  my last thread though). data mockdata1; input cat $ y1 y2 y3 y4 y5 yt; datalines; sex . . . . . . m 1 2 3 4 5 15 f 1 2 3 4 5 15 unknown . . 1 . 4 5 age . . . . . . age1 1 2 3 4 5 15 age2 1 2 3 4 5 15 age3 1 2 3 4 5 15 age4 1 2 3 4 5 15 age5 1 2 3 4 5 15 age6 1 2 3 4 5 15 missing 1 8 . . . 9 race . . . . . . race1 1 2 3 4 5 15 race2 1 2 3 4 5 15 race3 1 2 3 4 5 15 other 1 2 3 4 5 15 ; run; proc print data=mockdata1;run; data mockdata2; retain cat1; set mockdata1; if n(of y:)=0 then cat1=cat; run; proc print data=mockdata2;run; proc report data=mockdata2; column cat1 cat y1 y2 y3 y4 y5 yt; define cat1/group order=data noprint; define cat/'Variable'; define y1--yt/analysis sum; rbreak after/summarize style=[font_weight=bold]; break after cat1/summarize style=[font_style=italic]; run;   ... View more

Hi @CathyVI , it's looks to me that you do not have raw data, and the table (which you call it your sample dataset) you work on is in fact a report/result table in excel that has been imported to SAS as a SAS dataset, what you want to do is add the subtotal and total. My first suggestion is, calculate the subtotal and total in excel (which is much easier). If one does need to work on this in SAS, here is what I tried (with a mock dataset), I know the display/layout does not match your request, but I think my code can make the steps simpler and easier, especially if you want only the code for calculate the subtotal and total. BTW, my code is base on @Tom 's code and ideas, I just did minor changes,  and also simplified the code a bit according to @Ksharp 's suggestion. data mockdata1; input cat $ y1 y2 y3 y4 y5 yt; datalines; sex . . . . . . m 1 2 3 4 5 15 f 1 2 3 4 5 15 unknown . . 1 . 4 5 age . . . . . . age1 1 2 3 4 5 15 age2 1 2 3 4 5 15 age3 1 2 3 4 5 15 age4 1 2 3 4 5 15 age5 1 2 3 4 5 15 age6 1 2 3 4 5 15 missing 1 8 . . . 9 race . . . . . . race1 1 2 3 4 5 15 race2 1 2 3 4 5 15 race3 1 2 3 4 5 15 other 1 2 3 4 5 15 ; run; proc print data=mockdata1;run; data mockdata2; retain cat1; set mockdata1; if n(of y:)=0 then cat1=cat; run; proc print data=mockdata2;run; proc report data=mockdata2; column cat1 cat y1 y2 y3 y4 y5 yt; define cat1/group order noprint; define cat/'Variable'; define y1--yt/analysis sum; rbreak after/summarize style=[font_weight=bold]; break after cat1/summarize style=[font_style=italic]; run;   ... View more

Hi @Tom thanks for correct the syntax! Change the single quotation mark in prxmatch('//'); was the only thing that I did not try (I even tried something like prxmatch('/("&list")/') because I was not familar with perl (prxmatch) syntax and mistakenly believe that the single quotation mark was a fixed syntax in prxmatch() statement.   So now the most efficient and succinct answer (no array, no macro needed) for the question is as follows, and hi @SunnySong , what do you think😀?   data street_name; input st_nm $ typ 4.; datalines; ROAD 1 RD 1 STREET 1 ST 1 PLACE 1 PL 1 PO_BOX 3 UNIT 2 SUITE 2 ; run; data address; input address $20.; datalines; 3 ARRON ROAD 15 BONNY RD 7 DAISY STREET PO BOX 15 UNIT 6 89 ROSE PLACE 9 HOPE ST ; run; proc sql noprint; select st_nm into :strlist separated by '|' from street_name where typ=1; quit; data str_address; set address; loc=prxmatch("/(&strlist)/",address); if loc^=0 then found=1; run; proc print data=str_address;run; ... View more

Hi @SunnySong , would prxmatch make the answer easier? The code and results are as follows.   However, the loc1=; statement does not work because it seems that the &strlist  macro was not resolved. The loc=; statement worked because I copied the value of the &strlist into the statement manually. My furthur question is: how to make the loc1=prxmatch('/(&strlist)/',address); work, i.e., resolve the &strlist macro properly?   data street_name; input st_nm $ typ 4.; datalines; ROAD 1 RD 1 STREET 1 ST 1 PLACE 1 PL 1 PO_BOX 3 UNIT 2 SUITE 2 ; run; data address; input address $20.; datalines; 3 ARRON ROAD 15 BONNY RD 7 DAISY STREET PO BOX 15 UNIT 6 89 ROSE PLACE 9 HOPE ST ; run; proc sql noprint; select st_nm into :strlist separated by '|' from street_name where typ=1; quit; %put &strlist; /*ROAD|RD|STREET|ST|PLACE|PL*/ data str_address; set address; loc=prxmatch('/(ROAD|RD|STREET|ST|PLACE|PL)/',address); if loc^=0 then found=1; loc1=prxmatch('/(&strlist)/',address); run; proc print data=str_address;run; ... View more

Thanks a lot for your information and help, Rob! ... View more

Thanks a lot for this information! I will need to learn and try these steps (save my formats in particular library and re-use them) in near future I think! Thank you and all then best 😀 ... View more

Thanks a lot @JackieJ_SAS ! So the reason was I did not run the format program before I did that question. In fact I noticed the format; statement in the code and knew the format was not applied, and I have even tried options fmtsearch=(stat1); and did not work, then I had a wrong conclusion that lsmean pdiff=; statement does not allow a formatted value and need the original values. I'll run st100d05 and see the results for that question 😀, again, thanks a lot for let me know this, really helpful!       ... View more

However, my test result is different from the results in course video (see screen capture), do I get different dataset?    ... View more