data b;
input patientid date2 : mmddyy10. x1 x2 x3 x4 x5 x6 x7;
format date2 mmddyy10.;
datalines;
4 5/5/2009 Y . . . . . .
4 5/5/2009 . Y . . . . . .
4/5/5/2009 . . Y . . . .
4 5/6/2009 Y . . . . . .
4 5/6/2009 . Y . . . . . .
4/5/6/2009 . . Y . . . .
4 5/6/2009 . . . y . . .
4 5/6/2009 . . . . y . .
4/5/6/2009 . . . . . y .
5 5/5/2009 Y . . . . . .
5 5/5/2009 . Y . . . . . .
5/5/5/2009 . . Y . . . .;
run;
I have the following data entered in a way for which observations happening in the same day are entered into columns rather than the same row. I want to transpose it to the following format
id date2 x1 x2 x3 x4 x5 x6 x7
4 5/5/2009 Y Y Y . . . .
4/5/6/2009 Y Y Y Y Y Y .
5 5/5/2009 Y Y Y . . . .
There are a few error in your test data. However, you can do something like this
data b;
input patientid date2 : mmddyy10. (x1-x7)(:$);
format date2 mmddyy10.;
datalines;
4 5/5/2009 Y . . . . . .
4 5/5/2009 . Y . . . . . .
4 5/5/2009 . . Y . . . .
4 5/6/2009 Y . . . . . .
4 5/6/2009 . Y . . . . . .
4 5/6/2009 . . Y . . . .
4 5/6/2009 . . . y . . .
4 5/6/2009 . . . . y . .
4 5/6/2009 . . . . . y .
5 5/5/2009 Y . . . . . .
5 5/5/2009 . Y . . . . . .
5 5/5/2009 . . Y . . . .
;
data want;
update b(obs=0) b;
by patientid date2;
if last.date2;
run;
There are a few error in your test data. However, you can do something like this
data b;
input patientid date2 : mmddyy10. (x1-x7)(:$);
format date2 mmddyy10.;
datalines;
4 5/5/2009 Y . . . . . .
4 5/5/2009 . Y . . . . . .
4 5/5/2009 . . Y . . . .
4 5/6/2009 Y . . . . . .
4 5/6/2009 . Y . . . . . .
4 5/6/2009 . . Y . . . .
4 5/6/2009 . . . y . . .
4 5/6/2009 . . . . y . .
4 5/6/2009 . . . . . y .
5 5/5/2009 Y . . . . . .
5 5/5/2009 . Y . . . . . .
5 5/5/2009 . . Y . . . .
;
data want;
update b(obs=0) b;
by patientid date2;
if last.date2;
run;
Hi.
There was an issue with the data as there was forward slashes between patientid and date2.
I removed them and replaced Y with numbers to show that it is working.
Here is the code, if you want to try. Please let me know if this is what you wanted. I might have read the requirements wrong. After the code is the output
data b;
input patientid date2 : mmddyy10. x1 x2 x3 x4 x5 x6 x7;
format date2 mmddyy10.;
datalines;
4 5/5/2009 1 . . . . . .
4 5/5/2009 . 2 . . . . . .
4 5/5/2009 . . 3 . . . .
4 5/6/2009 4 . . . . . .
4 5/6/2009 . 5 . . . . . .
4 5/6/2009 . . 6 . . . .
4 5/6/2009 . . . 7 . . .
4 5/6/2009 . . . . 8 . .
4 5/6/2009 . . . . . 9 .
5 5/5/2009 1 . . . . . .
5 5/5/2009 . 2 . . . . . .
5/5/5/2009 . . 3 . . . .;
run;
PROC REPORT data=b;
COLUMNS patientid date2 (x1 x2 x3 x4 x5 x6 x7);
DEFINE patientid / GROUP;
DEFINE date2 / GROUP;
DEFINE x1 / ACROSS SUM;
DEFINE x2 / ACROSS SUM;
DEFINE x3 / ACROSS SUM;
DEFINE x4 / ACROSS SUM;
DEFINE x5 / ACROSS SUM;
DEFINE x6 / ACROSS SUM;
DEFINE x7 / ACROSS SUM;
RUN;
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 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.