topic Re: BASE SAS Code help in SAS Programming

BASE SAS Code help

InnovativeMinds — Thu, 20 Apr 2017 00:28:22 GMT

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

Re: BASE SAS Code help

Jagadishkatam — Thu, 20 Apr 2017 01:03:13 GMT

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;

Re: BASE SAS Code help

ChrisNZ — Thu, 20 Apr 2017 05:44:39 GMT

Why is there no key2=905 in the output?

Re: BASE SAS Code help

InnovativeMinds — Thu, 20 Apr 2017 08:54:53 GMT

Chris, sorry that missed 904&905. Thanks for the catch!

Re: BASE SAS Code help

InnovativeMinds — Thu, 20 Apr 2017 08:55:43 GMT

Thanks Jagadishkatam.