DATA Step, Macro, Functions and more

BASE SAS Code help

Reply
New Contributor
Posts: 4

BASE SAS Code help

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:

key1key2
101901
101902
101903
101904
101905
102801
102802
102803
102804

 

2nd Input Dataset:

key1key3key4var1var2var3var4var5varn
101a1b1......
101a2b2......
101a3b3......
102a4b4......
102a5b5......
102a6b6......

 

Output dataset:

key2key1key3key4var1var2var3var4var5varn
901101a1b1......
901101a2b2......
901101a3b3......
902101a1b1......
902101a2b2......
902101a3b3......
903101a1b1......
903101a2b2......
903101a3b3......
801102a4b4......
801102a5b5......
801102a6b6......
802102a4b4......
802102a5b5......
802102a6b6......
803102a4b4......
803102a5b5......
803102a6b6......
804102a4b4......
804102a5b5......
804102a6b6......

 

Thanks Again!

John

Trusted Advisor
Posts: 1,131

Re: BASE SAS Code help

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;
Thanks,
Jag
PROC Star
Posts: 1,564

Re: BASE SAS Code help

Why is there no key2=905 in the output?

New Contributor
Posts: 4

Re: BASE SAS Code help

Chris, sorry that missed 904&905. Thanks for the catch!
New Contributor
Posts: 4

Re: BASE SAS Code help

Thanks Jagadishkatam.
Ask a Question
Discussion stats
  • 4 replies
  • 108 views
  • 0 likes
  • 3 in conversation