topic Merging with same ID considered distinctly in SAS Programming

Merging with same ID considered distinctly

Fcot — Tue, 12 Dec 2017 05:18:26 GMT

Hi all,

I have a question about merging datasets. I'll try to state my problem as clearly as possible.

Dataset #1:
Fund Target Permno
Fund_A ABC 10101
Fund_B ABC 10101
Fund_C ABC 10101

Dataset #2
Target Permno Return Date
ABC 10101 0.01 1
ABC 10101 0.02 2
... ... ... ...
ABC 10101 0.00 1000

If I do a simple merge by Permno, I only get the match for one Fund, while the other disappear.

I would like to merge by Permno (only shared variable), but by having every fund treated as an independent observations in the merging:

Desired merged dataset
Fund Target Permno Return Date
Fund_A ABC 10101 0.01 1
... ... ... ... ...
Fund_A ABC 10101 0.00 1000
Fund_B ABC 10101 0.01 1
... ... ... ... ...
Fund_B ABC 10101 0.00 1000
Fund_C ABC 10101 0.01 1
... ... ... ... ...
Fund_C ABC 10101 0.00 1000

Hopefully my explanations are clear. The answer may be easy but my limited vocabulary about SAS limits my "googling" abilities.

Thank you,

Re: Merging with same ID considered distinctly

RM6 — Tue, 12 Dec 2017 05:38:32 GMT

try using merge by target permno

Re: Merging with same ID considered distinctly

PeterClemmensen — Tue, 12 Dec 2017 07:01:25 GMT

I think this is what you want. I inserted 5 observations for each fund instead of 1000 for demonstration purposes 🙂

data one;
input Fund$ Target$ Permno$;
datalines;
Fund_A ABC 10101
Fund_B ABC 10101
Fund_C ABC 10101
;

data two;
input Target$ Permno$ Return Date;
datalines;
ABC 10101 0.01 1
ABC 10101 0.02 2
ABC 10101 0.02 3
ABC 10101 0.02 4
ABC 10101 0.02 5
;

proc sql;
	create table want as
	select one.*
	      ,two.return
		  ,two.date
	from one, two;
quit;

proc print data=want;
run;

Re: Merging with same ID considered distinctly

Astounding — Tue, 12 Dec 2017 14:10:09 GMT

The solution from @PeterClemmensen looks about right. In real life, you may have many values of PERMNO in the data set. If that's the case, you would have to add a WHERE clause to the CREATE statement:

where one.permo = two.permno;

Re: Merging with same ID considered distinctly

Fcot — Tue, 12 Dec 2017 18:28:35 GMT

Thank you very much for your help! It works