Hi
I have two datasets a and b that need t o be joined in a peculiar way to create dataset c
The common link for dataset a and b is the Refer_ID
Each refer_ID can only appear once, therefore, in a) the Refer_ID is recuced to 1 and 2 with Song_1 and Song_2
Then dataset b) needs to join in there, whereby I may have some left joins to create a new column for
Role_2 | Role_3 | Role_4 | Role_5 | Role_6 | Role_7 |
as shownbelow in c) basically shift there Role_2 to Role_7 across and along in line with the Refer_ID it belongs to.
How can this be archievedd? Many thanks!
a | Refer_ID | Person |
1 | Song_1 | |
1 | Song_1 | |
1 | Song_1 | |
2 | Song_2 | |
2 | Song_2 | |
2 | Song_2 | |
2 | Song_2 | |
2 | Song_2 | |
2 | Song_2 | |
2 | Song_2 |
b | Refer_ID | Role |
1 | Role_1 | |
1 | Role_2 | |
1 | Role_3 | |
2 | Role_1 | |
2 | Role_2 | |
2 | Role_3 | |
2 | Role_4 | |
2 | Role_5 | |
2 | Role_6 | |
2 | Role_7 |
c | Refer_ID | Person | Role | Role_2 | Role_3 | Role_4 | Role_5 | Role_6 | Role_7 |
1 | Song_1 | Role_1 | Role_2 | Role_3 | |||||
2 | Song_2 | Role_1 | Role_2 | Role_3 | Role_4 | Role_5 | Role_6 | Role_7 |
Something like this?
data person;
length refer_id 8 person $32;
input refer_id person;
datalines;
1 Song_1
1 Song_1
1 Song_1
2 Song_2
2 Song_2
2 Song_2
2 Song_2
2 Song_2
2 Song_2
2 Song_2
;
run;
data role;
length refer_id 8 role $32;
input refer_id role;
datalines;
1 Role_1
1 Role_2
1 Role_3
2 Role_1
2 Role_2
2 Role_3
2 Role_4
2 Role_5
2 Role_6
2 Role_7
;
run;
proc sort data=person nodupkey;
by refer_id person;
run;
proc transpose data=role out=role_t(drop=_name_);
by refer_id;
id role;
var role;
run;
data one;
merge person role_t;
by refer_id;
run;
Thats not a join then, its a transpose. Post test data in the form of a datastep to get working code (its probably been mentioned several times before), as such this code is just an un-tested guess:
proc transpose data=datab out=datab_t; by refer_id; var role; id role; run; proc sort data=dataa nodupkey; by refer_id person; run; data want; merge dataa datab; by refer_id; run;
Not sure what value there is in creating a whole set of cells with the same data though, the table c just seems to bloat the data to my mind.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.