I'm trying to write a macro that can take any data set and any key variables and convert from wide form to long form. I'm having a hard time visualizing how this is possible.
Don't write your own macro. Use the %UNTRANSPOSE macro
https://www.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2018/2419-2018.pdf
I eventually want to apply it to this data set
That would be a PROC TRANSPOSE.
proc transpose data=have out=want;
by ID GENDER;
VAR age: ;
run;
I suppose you could make it a macro but I don't see a huge amount of value in that beyond using the procedure. Here are some tutorials on transposing if you want to understand the different option in SAS further. There are also many user macros already written, this is one written by some of the users here.
Transposing data tutorials:
Long to Wide:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/
https://stats.idre.ucla.edu/sas/modules/reshaping-data-long-to-wide-using-the-data-step/
Wide to Long:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/
https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/
And sometimes a double transpose is needed for extra wide data sets:
https://gist.github.com/statgeek/2321b6f62ab78d5bf2b0a5a8626bd7cd
Before writing the macro think about what SAS code you want to run. Then you can start thinking about how to generate that code using macro logic.
Are you asking for something different than PROC TRANSPOSE.
proc transpose data=have out=want ;
by key1 key2 ;
var _all_ ;
run;
Don't write your own macro. Use the %UNTRANSPOSE macro
https://www.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2018/2419-2018.pdf
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.