topic Re: From One Variable to Multiple Variables in SAS Programming

From One Variable to Multiple Variables

jporterf — Thu, 10 Dec 2020 15:21:32 GMT

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!

Re: From One Variable to Multiple Variables

PGStats — Sat, 07 Nov 2020 22:06:11 GMT

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;

Re: From One Variable to Multiple Variables

PaigeMiller — Sat, 07 Nov 2020 22:10:18 GMT

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.

Re: From One Variable to Multiple Variables

Reeza — Sat, 07 Nov 2020 22:57:11 GMT

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

Re: From One Variable to Multiple Variables

jporterf — Sun, 08 Nov 2020 22:50:15 GMT

The desired output is SAS data set. I need to combine this data set with two other ones to create a final data set.

Re: From One Variable to Multiple Variables

jporterf — Sun, 08 Nov 2020 22:50:53 GMT

I thought this might deal with re-structuring data, thank you!