topic Merging datasets where values of a variable become variables in output dataset in SAS Programming

Merging datasets where values of a variable become variables in output dataset

knveraraju91 — Sat, 18 Feb 2017 18:29:51 GMT

Dear,

I am merging the two datsets. I got the output I need by several datasteps. But i want to do a better way and reduce the code.Please suggest. Thank you

data one;
input id seq;
datalines;
1 1
1 2
2 2
2 3
3 1
4 2
5 4
;

data suppone;
input id idvar $ nam $ val $;
datalines;
1 1 act aaa
2 3 act aab
2 3 act1 bbb
3 1 act ccc
3 1 act1 cc1
3 1 act2 cc2
4 2 act ddd
5 4 act eee
5 4 act1 ee1
;

/*The suppone dataset in split into multiple datasets by "nam" varibale*/

data act act1 act2 act3;
set suppone;
if nam='act' then output act;
if nam='act1' then output act1;
if nam='act2' then output act2;
;

data sact;
set act;
seq=input(idvarval,best.);
rename val=act;
drop nam;
run;

Repeated the above step for all and then merged with dataone by id seq.

output needed:

ID seq act act1 act2
1 1 aaa
1 2
2 2
2 3 bbb bb1
3 1 ccc cc1 cc2
4 2 ddd
5 4 eee ee1

Re: Merging datasets where values of a variable become variables in output dataset

art297 — Sat, 18 Feb 2017 19:38:23 GMT

Assuming that your desired output contains the following error:

2 3 bbb bb1 should really be 2 3 aab bb1

then the following would be easier:

data new_suppone (drop=_:);
  set suppone (rename=(idvar=_idvar));
  idvar=input(_idvar, 8.);
run;

proc transpose data=new_suppone out=supponet ( drop=_: rename=(idvar=seq));
  var val;
  id nam;
  by id idvar;
run;

data want;
  merge one supponet;
  by id seq;
run;

Re: Merging datasets where values of a variable become variables in output dataset

LinusH — Sun, 19 Feb 2017 06:05:26 GMT

For the merging I would use SQL since it's more flexible and allows manipulation of join column in the same step.
Your end result looks more like a report and for that I would suggest PROC TABULATE or REPORT.