Consolidate values from duplicate records to new variables in a single record

[bkq32]

Hi everyone,

I have this data set with duplicate IDs:

data have;
 format id $1. site $2. date mmddyy10. nlev $1.;
 input id$ site$ date mmddyy8. nlev$;
 cards;
 A L1 01012001 1
 A L3 02022002 2
 B E3 03032003 1
 B W3 04042004 2
 B R4 05052005 3
 ;
run;

and I want to end up with one record per ID, but create new variables that will hold the values from the duplicate records to get something like this:

data want;
 format id $1. site1 $2. date1 mmddyy10. site2 $2. date2 mmddyy10. site3 $2. date3 mmddyy10.;
  input id$ site1$ date1 mmddyy8. site2$ date2 mmddyy8. site3$ date3 mmddyy8.;
  cards;
  A L1 01012001 L3 02022002 . .
  B E3 03032003 W3 04042004 R4 05052005
  ;
 run;

I created the NLEV variable to help me identify the duplicate IDs, but I don't need it in the final data set. Could someone give me some suggestions as to how I should go about doing this?

[PeterClemmensen · Accepted solution]

@bkq32 Hi and welcome to the SAS Community!

Provided that the nlev variable exists, you can do like this

data have;
 format id $1. site $2. date mmddyy10. nlev $1.;
 input id$ site$ date mmddyy8. nlev$;
 cards;
 A L1 01012001 1
 A L3 02022002 2
 B E3 03032003 1
 B W3 04042004 2
 B R4 05052005 3
 ;
run;

proc sql noprint;
	select max(nlev) into :n from have;
quit;

%put &n.;

data want;
	do _N_=1 by 1 until (last.id);
		set have(rename=(date=_date site=_site));
		by id;
		array site{&n.} $2;
		array date{&n.};

		site[_N_]=_site;
		date[_N_]=_date;
	end;

	keep id site: date:;
	format date: mmddyyn8.;
run;

[Astounding]

It is also possible to do this with PROC TRANSPOSE. If you take that approach you don't need to know NLEV. However you would have to run it twice (once to transpose SITE and once to transpose DATE)

[Ksharp]

data have;
 format id $1. site $2. date mmddyy10. nlev $1.;
 input id$ site$ date mmddyy8. nlev$;
 cards;
 A L1 01012001 1
 A L3 02022002 2
 B E3 03032003 1
 B W3 04042004 2
 B R4 05052005 3
 ;
run;
proc sql;
create table level as
select distinct nlev from have;
quit;

data _null_;
 set level end=last;
 if _n_=1 then call execute('data want;merge ');
 call execute(catt('have(where=(nlev="',nlev,'") 
 rename=(site=site_',nlev,' date=date_',nlev,'))'));
 if last then call execute(';by id;drop nlev;run;');
run;

[bkq32]

Thank you all!