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
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.