topic Re: Consolidate values from duplicate records to new variables in a single record in New SAS User

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

bkq32 — Fri, 06 Sep 2019 05:21:46 GMT

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?

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

PeterClemmensen — Fri, 06 Sep 2019 05:48:34 GMT

@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;

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

Astounding — Fri, 06 Sep 2019 09:54:11 GMT

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)

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

Ksharp — Fri, 06 Sep 2019 12:16:36 GMT

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;

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

bkq32 — Fri, 06 Sep 2019 17:12:59 GMT

Thank you all!