Joining across different datasets to create a shifted dataset

[AnnaNZ]

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

[FredrikE]

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;

[RW9]

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.