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,
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;
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;
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;
Thank you very much for your help! It works
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.