Hello! I hope my title makes sense.
So here is the output data set I have for one of my data sets:
Var1 | Var2 | Var3 | Var4 |
01JAN2010 | Height | 1 | 111-11-1111 |
01JAN2010 | Weight | 2 | 111-11-1111 |
01JAN2010 | Systolic BP | 3 | 111-11-1111 |
01JAN2010 | Diastolic BP | 4 | 111-11-1111 |
06JAN2011 | Height | 5 | 111-11-1111 |
06JAN2011 | Weight | 6 | 111-11-1111 |
06JAN2011 | Systolic BP | 7 | 111-11-1111 |
06JAN2011 | Diastolic BP | 8 | 111-11-1111 |
Here is what I need it to look like (this is a separate output data set I've already completed):
Var1 | Var2 | Var3 | Var4 | Var5 | Var6 |
111-11-1111 | 30AUG2010 | 1 | 1 | 1 | 1 |
111-11-1111 | 22AUG2011 | 2 | 2 | 2 | 2 |
111-11-1111 | 31AUG2012 | 3 | 3 | 3 | 3 |
111-11-1111 | 05SEP2013 | 4 | 4 | 4 | 4 |
111-11-1111 | 12SEP2014 | 5 | 5 | 5 | 5 |
I need each subject to have 4 observations organized by visit date, height, weight, SBP, and DBP. Currently, in the first data set, each subject has 16 observations. The variables currently include height, weight, SBP, DBP, and the values for each, and I need each of those to be their own variable and value. And then I need only 1 observation for each date for each SSN instead of 4 observations for each date and SSN.
I'm honestly not sure where to even begin. If I can get some guidance (even if it's just what functions to look into myself) I would greatly appreciate it!
Pre process your Measure field to transform the measure names into your expected variable names
if measure = 'Height (In)' then varname = "HtIn";
...
etc.
Then you can simply sort and transpose your dataset
proc sort ...; by SSN VisitDT;
proc transpose ... ;
by SSN VisitDt;
ID varname;
var value;
Please clarify something. Is the desired output a table that you can e-mail or put in a report? Or is the desired output a SAS data set?
Either way, do not try doing this in a SAS data step. Either PROC REPORT or PROC TRANSPOSE will be the easiest solution.
The desired output is SAS data set. I need to combine this data set with two other ones to create a final data set.
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
I thought this might deal with re-structuring data, thank you!
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: