Hello
I use the following macro to transpose my tables according to a single variable and I would like to introduce a second variable.
Can anyone help me for this?
Thanks
%macro transpose_B( vis=, vis2= ); data table_&vis; set table; if visit=&vis2; keep id visit var1 var2 var3 var4 var5 var6 ; run; proc sort data=table_&vis nodupkey; by id ; run; data table_&vis; set table_&vis; drop VISIT; rename var1=var1_&vis; rename var2=var2_&vis; rename var3=var3_&vis; rename var4=var4_&vis; rename var5=var5_&vis; rename var6=var6_&vis; run; proc sort data=table_&vis; by id; run; %mend; /*Run the transpose_B macro */ %transpose_B(vis=D1, vis2="Baseline");
@Liamb The %TRANSPOSE macro that I gave a link to will do this.
It's not clear to me what you mean by "introduce a second variable". What would this second variable do?
It's also not clear to me how this is a transpose.
Nevertheless, TRANSPOSE macros are available, you don't have to write your own.
Use the %TRANSPOSE macro at the link I gave
Repeated use of code like this with the same data set on Set and Data statements in the body of macro code can make it extremely difficult to trace exactly where your logic had an error when debugging the data.
data table_&vis; set table_&vis;
I also agree with @PaigeMiller that there is no "transposing" going on here. All I see is sub-setting a dataset and renaming (clumsily) a bunch of variables.
With that in mind, what do you intend to do with that second variable?
Show the code that performed the entire task you are attempting, using two variables, without any macro code. It is extremely difficult to program macros properly without non-macro code that works before attempting the macro. The SAS macro language creates code statements. If you do not know what statements you need to generate then you won't be able to code a macro to create them.
I have a dataset as below.
ID | VISIT | DATE | LAB_ID | LAB_V | LAB_U | LAB_NORMAL |
A1 | D1 | 15/04/2022 | HB | 12 | d/dl | YES |
A1 | D2 | 16/04/2022 | CREAT | 20 | umol/l | YES |
A1 | D3 | 17/04/2022 | CRP | 100 | mg/l | NO |
A1 | D4 | 18/04/2022 | AST | 70 | U/l | YES |
A1 | D5 | 19/04/2022 | ALT | 90 | U/l | NO |
A2 | D1 | 20/04/2022 | HB | 10 | YES | |
A2 | D2 | 21/04/2022 | CREAT | 30 | YES | |
A2 | D3 | 22/04/2022 | CRP | 80 | YES | |
A2 | D4 | 23/04/2022 | AST | 50 | YES | |
A2 | D5 | 24/04/2022 | ALT | 40 | YES | |
A3 | D1 | 25/04/2022 | HB | 6 | NO | |
A3 | D2 | 26/04/2022 | CREAT | 10 | NO | |
A3 | D3 | 27/04/2022 | CRP | 400 | NO | |
A3 | D4 | 28/04/2022 | AST | 200 | NO | |
A3 | D5 | 29/04/2022 | ALT | 200 | NO |
The macro I sent you allows me to transpose the table and add the name of the visit to the name of each variable then I do a merge of all my tables (table_d1 table_d2 table_d3 table_d4 table_d5).
And I want to transpose my table with visit and lab_id which will give me table_hb_d1 table_hb_d2.......
@Liamb The %TRANSPOSE macro that I gave a link to will do this.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.