topic Re: Proc sql macro in loop in SAS Programming

Proc sql macro in loop

nyy2 — Thu, 14 Jul 2022 21:36:33 GMT

hello--

I have a wide dataset (100 columns) that I would like to transpose by patientID to long. I have 30 columns that all have the same prefix diag_1,diag_2, etc.

Is there a way to write macro within a proc sql loop that would select union through each of the diag_ variables?

tia!

Re: Proc sql macro in loop

Reeza — Thu, 14 Jul 2022 21:41:01 GMT

Why not use PROC TRANSPOSE instead?

Re: Proc sql macro in loop

ballardw — Thu, 14 Jul 2022 22:51:15 GMT

What are the other 69 columns? Or do you not want them included in the long set?

Does PatientId repeat in the data, such as on different dates or different facilities? Is the date/facility/other information to be in the result?

Assuming that you have a date along with the patient Id this is how I would start this:

Proc sort data=have;
   by PatientId Visitdate;
run;

Proc transpose data=have out=long;
   by patientid visitdate;
   var diag_: ;
run;

You want to include enough variables in the BY statement in both Proc Sort and transpose to uniquely identify the source record. The Diag_: is a list that tells SAS to use all the variables whose names start with Diag_ . If there are some that are different than your numbered variables that you would not want then use the list Diag_1 - Diag_30 (assumes they are sequentially numbered).

Re: Proc sql macro in loop

Reeza — Thu, 14 Jul 2022 23:06:57 GMT

Basically there's a few ways to transpose data and SQL is the last option in the list of options by preference because you have to manually do everything. Using a data step and array, proc transpose are the best options.

Transposing data tutorials:
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/