topic Partially combine multiple rows to a single row in SAS Programming

Partially combine multiple rows to a single row

_MooMoo — Mon, 09 Sep 2019 21:52:35 GMT

Hello,

I am pretty new to this community but reaching out for help to unravel the difficulty I am having.

The sample data set is....

data have;
	input ID epi_1 : mmddyy10. epi_2 : mmddyy10. epi_3 : mmddyy10. ;
	format epi_1 mmddyy10. epi_2 mmddyy10. epi_3 mmddyy10.;
	datalines;
	1 01/01/2019 02/01/2019 .
	1 03/01/2019 .          .
	2 04/01/2019 .          .
	2 05/01/2019 .          .
	2 06/01/2019 07/01/2019 .
;
run;

I am trying to transpose the data set by ID but I only want to keep non missing columns and change the column name in sequence (the column name of the first episode should be epi_1).

Below is the data set I want to output:

Any suggestions are welcome! Thanks in advance!

Re: Partially combine multiple rows to a single row

novinosrin — Tue, 10 Sep 2019 00:35:35 GMT

Hello @_MooMoo Welcome to SAS communities as it's your 1st post.

See if this helps

data have;
	input ID epi_1 : mmddyy10. epi_2 : mmddyy10. epi_3 : mmddyy10. ;
	format epi_1 mmddyy10. epi_2 mmddyy10. epi_3 mmddyy10.;
	datalines;
	1 01/01/2019 02/01/2019 .
	1 03/01/2019 .          .
	2 04/01/2019 .          .
	2 05/01/2019 .          .
	2 06/01/2019 07/01/2019 .
;
run;

proc transpose data=have out=t(where=(col1 ne .) keep=id col1);
by id ep:;
var ep:	;
run;

proc transpose data=t out=want(drop=_:) prefix=Ep_;
by id ;
var col1;
run;

Kind Regards!

Re: Partially combine multiple rows to a single row

_MooMoo — Tue, 10 Sep 2019 13:57:23 GMT

Thanks! Didn't think this way.