Hi, I have two input datasets and I want to create an output dataset like mentioned below. Thanks so much for your help and time.
1st input dataset:
key1 | key2 |
101 | 901 |
101 | 902 |
101 | 903 |
101 | 904 |
101 | 905 |
102 | 801 |
102 | 802 |
102 | 803 |
102 | 804 |
2nd Input Dataset:
key1 | key3 | key4 | var1 | var2 | var3 | var4 | var5 | varn |
101 | a1 | b1 | . | . | . | . | . | . |
101 | a2 | b2 | . | . | . | . | . | . |
101 | a3 | b3 | . | . | . | . | . | . |
102 | a4 | b4 | . | . | . | . | . | . |
102 | a5 | b5 | . | . | . | . | . | . |
102 | a6 | b6 | . | . | . | . | . | . |
Output dataset:
key2 | key1 | key3 | key4 | var1 | var2 | var3 | var4 | var5 | varn |
901 | 101 | a1 | b1 | . | . | . | . | . | . |
901 | 101 | a2 | b2 | . | . | . | . | . | . |
901 | 101 | a3 | b3 | . | . | . | . | . | . |
902 | 101 | a1 | b1 | . | . | . | . | . | . |
902 | 101 | a2 | b2 | . | . | . | . | . | . |
902 | 101 | a3 | b3 | . | . | . | . | . | . |
903 | 101 | a1 | b1 | . | . | . | . | . | . |
903 | 101 | a2 | b2 | . | . | . | . | . | . |
903 | 101 | a3 | b3 | . | . | . | . | . | . |
801 | 102 | a4 | b4 | . | . | . | . | . | . |
801 | 102 | a5 | b5 | . | . | . | . | . | . |
801 | 102 | a6 | b6 | . | . | . | . | . | . |
802 | 102 | a4 | b4 | . | . | . | . | . | . |
802 | 102 | a5 | b5 | . | . | . | . | . | . |
802 | 102 | a6 | b6 | . | . | . | . | . | . |
803 | 102 | a4 | b4 | . | . | . | . | . | . |
803 | 102 | a5 | b5 | . | . | . | . | . | . |
803 | 102 | a6 | b6 | . | . | . | . | . | . |
804 | 102 | a4 | b4 | . | . | . | . | . | . |
804 | 102 | a5 | b5 | . | . | . | . | . | . |
804 | 102 | a6 | b6 | . | . | . | . | . | . |
Thanks Again!
John
You are trying to get a cartesian product which is possible by proc sql as below
data have1;
input key1 key2;
cards;
101 901
101 902
101 903
101 904
101 905
102 801
102 802
102 803
102 804
;
data have2;
input key1 key3$ key4$ var1 var2 var3 var4 var5 varn;
cards;
101 a1 b1 . . . . . .
101 a2 b2 . . . . . .
101 a3 b3 . . . . . .
102 a4 b4 . . . . . .
102 a5 b5 . . . . . .
102 a6 b6 . . . . . .
;
proc sql;
create table want as select a.*,b.key2 from have2 as a left join have1 as b on a.key1=b.key1 order by a.key1,b.key2;
quit;
Why is there no key2=905 in the output?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.