topic Macro in SAS Programming

Macro

matoma — Sat, 25 Apr 2020 17:39:46 GMT

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.

Re: Macro

matoma — Sat, 25 Apr 2020 17:41:32 GMT

I eventually want to apply it to this data set

Re: Macro

Tom — Sat, 25 Apr 2020 17:57:50 GMT

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;

Re: Macro

PaigeMiller — Sat, 25 Apr 2020 17:58:08 GMT

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

Re: Macro

Reeza — Sat, 25 Apr 2020 18:23:00 GMT

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.

https://communities.sas.com/t5/SAS-Communities-Library/A-better-way-to-FLIP-i-e-transpose-make-wide-a-dataset/ta-p/433620

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