DATA Step, Macro, Functions and more

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

Reply
Super Contributor
Posts: 272

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

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

 

 

PROC Star
Posts: 7,468

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

Posted in reply to knveraraju91

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;
Super User
Posts: 5,425

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

Posted in reply to knveraraju91
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.
Data never sleeps
Ask a Question
Discussion stats
  • 2 replies
  • 140 views
  • 3 likes
  • 3 in conversation