06-23-2016 06:24 AM
There are 2 datasets, names and partners. In partners , id is given but we need to find corresponding names using "names" dataset
input st_id st_names $;
input st_id1 st_id2 st_id3;
1 4 7
2 5 8
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?
06-23-2016 06:40 AM
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?
06-23-2016 06:51 AM
Transpose the partners dataset into long format first, then do the join. Don't keep data (number of partner) in structure (column name).
06-23-2016 07:07 AM
In actual scenario, names dataset has 80000 + records and partners has million of records , so cant do the transpose.
A puny little million is no problem for SAS.
06-23-2016 08:41 AM
Create a format out of names dataset and then apply format.
proc fmt cntlin=fmt_def;
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.);