BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lillymaginta
Lapis Lazuli | Level 10
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   .    .    .    . 

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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;

View solution in original post

4 REPLIES 4
PeterClemmensen
Tourmaline | Level 20

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;
koyelghosh
Lapis Lazuli | Level 10
Elegant
koyelghosh
Lapis Lazuli | Level 10

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;

 

 

OutputOutput

lillymaginta
Lapis Lazuli | Level 10

Thank you! 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 989 views
  • 3 likes
  • 3 in conversation