BookmarkSubscribeRSS Feed
AnnaNZ
Quartz | Level 8

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
2 REPLIES 2
FredrikE
Rhodochrosite | Level 12

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
Diamond | Level 26 RW9
Diamond | Level 26

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.

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
  • 299 views
  • 1 like
  • 3 in conversation