topic condensing a dataset in SAS Procedures

condensing a dataset

Paige1 — Fri, 11 Oct 2019 20:51:09 GMT

I have a dataset with ID, the date of their procedure, and the procedure name. Here's what it looks like:

ID Date procedure

1 1/1/2010 A

2 1/2/2010 A

2 1/2/2010 B

3 1/3/2010 A

3 1/3/2010 B

3 1/3/2010 C

I would like to have just one row per encounter. So if they have the same ID and date, they should be in the same row. So here is what I want the data to look like:

ID Date Procedure1 Procedure2 Procedure 3

1 1/1/2010 A . .

2 1/2/2010 A B .

3 1/3/2010 A B C

How can I make that happen?

(Using SAS 9.4)

Re: condensing a dataset

novinosrin — Sun, 13 Oct 2019 19:09:19 GMT

Hi @Paige1 See if this helps


data have;
input ID     Date   :mmddyy10.         procedure $;
format date mmddyy10.;
cards;
1      1/1/2010          A
2      1/2/2010          A
2      1/2/2010          B
3      1/3/2010          A
3      1/3/2010          B
3      1/3/2010          C
;

/*Identify and initialize the max count of ID for each by group*/
proc sql noprint;
select max(c) into :c trimmed
from (select count(*) as c from have group by id,date);
quit;

proc summary nway data=have missing; 
 class id date;
 output out = want(drop=_type_ _freq_) 
 idgroup(out[&c](Procedure)=) ;
run;

Re: condensing a dataset

novinosrin — Sun, 13 Oct 2019 19:09:49 GMT


data have;
input ID     Date   :mmddyy10.         procedure $;
format date mmddyy10.;
cards;
1      1/1/2010          A
2      1/2/2010          A
2      1/2/2010          B
3      1/3/2010          A
3      1/3/2010          B
3      1/3/2010          C
;

/*Identify and initialize the max count of ID for each by group*/
proc sql noprint;
select max(c) into :c trimmed
from (select count(*) as c from have group by id,date);
quit;

data want;
 do _n_=1 by 1 until(last.date);
  set have;
  by id date;
  array P(*)$ Procedure1-Procedure&c;
  p(_n_)=Procedure;
 end;
run;

Re: condensing a dataset

novinosrin — Fri, 11 Oct 2019 21:04:31 GMT


data have;
input ID     Date   :mmddyy10.         procedure $;
format date mmddyy10.;
cards;
1      1/1/2010          A
2      1/2/2010          A
2      1/2/2010          B
3      1/3/2010          A
3      1/3/2010          B
3      1/3/2010          C
;

proc transpose data=have out=want(drop=_name_) prefix=procedure;
by id date;
var procedure;
run;