topic Re: transpose data with dates in SAS Programming

transpose data with dates

lillymaginta — Fri, 28 Jun 2019 13:30:57 GMT

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   .    .    .    .

Re: transpose data with dates

PeterClemmensen — Fri, 28 Jun 2019 13:46:35 GMT

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;

Re: transpose data with dates

koyelghosh — Fri, 28 Jun 2019 14:02:25 GMT

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;

Output

Re: transpose data with dates

koyelghosh — Fri, 28 Jun 2019 14:05:26 GMT

Elegant

Re: transpose data with dates

lillymaginta — Fri, 28 Jun 2019 14:08:42 GMT

Thank you!