Joining across different datasets to create a shifted dataset

Reply
Contributor
Posts: 58

Joining across different datasets to create a shifted dataset

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_2Role_3Role_4Role_5Role_6Role_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!

 

 

aRefer_IDPerson
 1Song_1
 1Song_1
 1Song_1
 2Song_2
 2Song_2
 2Song_2
 2Song_2
 2Song_2
 2Song_2
 2Song_2

 

bRefer_IDRole
 1Role_1
 1Role_2
 1Role_3
 2Role_1
 2Role_2
 2Role_3
 2Role_4
 2Role_5
 2Role_6
 2Role_7

 

 

cRefer_IDPersonRoleRole_2Role_3Role_4Role_5Role_6Role_7
 1Song_1Role_1Role_2Role_3    
 2Song_2Role_1Role_2Role_3Role_4Role_5Role_6Role_7
Regular Contributor
Posts: 187

Re: Joining across different datasets to create a shifted dataset

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;

Super User
Super User
Posts: 7,720

Re: Joining across different datasets to create a shifted dataset

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.

Ask a Question
Discussion stats
  • 2 replies
  • 97 views
  • 1 like
  • 3 in conversation