BookmarkSubscribeRSS Feed
Ps8813
Fluorite | Level 6

There are 2 datasets, names and partners. In partners , id is given but we need to find corresponding names using "names" dataset

data names;
input st_id st_names $;
datalines;
1 John
2 Mark
3 Steve
4 Smith
5 Cook
6 Messi
7 Mary
8 Kris
9 Kevin
;
run;


data partners;
input st_id1 st_id2 st_id3;
datalines;
1 4 7
2 5 8
;
run;

I need to add 3 columns in dataset partners name_id1,name_id2,name_id3 . To get names from dataset "names" as per id , i think we need to join dataset 3 times. What if datasets has million of records, is there any way we can simply the joins?

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

How does parteners dataset relate to the first dataset?  I am confused as there are three name fields in it, but a name could only have one partner no?  If we assume the the first column is the person matching st_id then:

data names;
input st_id st_names $;
datalines;
1 John
2 Mark
3 Steve
4 Smith
5 Cook
6 Messi
7 Mary
8 Kris
9 Kevin
;
run;

data partners;
input st_id1 st_id2 st_id3;
datalines;
1 4 7
2 5 8
;
run;

proc sql;
  create table WANT as
  select  A.*,
          (select ST_NAMES from NAMES where ST_ID=B.ST_ID2) as ST_NAME2,
          (select ST_NAMES from NAMES where ST_ID=B.ST_ID3) as ST_NAME3
  from    NAMES A
  left join PARTNERS B
  on      A.ST_ID=B.ST_ID1;
quit;

But that only gives the first two records two partners?

Ps8813
Fluorite | Level 6

In actual scenario, names dataset has 80000 + records and partners has million of records , so cant do the transpose.

Kurt_Bremser
Super User

@Ps8813 wrote:

In actual scenario, names dataset has 80000 + records and partners has million of records , so cant do the transpose.


And?

A puny little million is no problem for SAS.

Reeza
Super User

Create a format out of names dataset and then apply format. 

 

Data fmt_def;

set table1;

fmtname='name_fmt';

start=st_id;

label=st_name;

run;

 

proc fmt cntlin=fmt_def;

run;

 

data want;

set table2;

array n_init (3) st_id1-st_id3;

array n_res(3) $ st_id_name1 - st_id_name3;

 

do i  = 1 to 3;

n_res (I)= put(n_init(I), name_fmt.);

end;

run;

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1611 views
  • 6 likes
  • 4 in conversation