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

[knveraraju91]

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

[art297]

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;

[LinusH]

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.