BookmarkSubscribeRSS Feed
knveraraju91
Barite | Level 11

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

 

 

2 REPLIES 2
art297
Opal | Level 21

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
Tourmaline | Level 20
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 668 views
  • 3 likes
  • 3 in conversation