Merging with same ID considered distinctly

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Merging with same ID considered distinctly

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,


Accepted Solutions
Solution
‎12-12-2017 01:28 PM
PROC Star
Posts: 1,083

Re: Merging with same ID considered distinctly

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

 

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;

View solution in original post


All Replies
Contributor RM6
Contributor
Posts: 24

Re: Merging with same ID considered distinctly

try using merge by target permno
Solution
‎12-12-2017 01:28 PM
PROC Star
Posts: 1,083

Re: Merging with same ID considered distinctly

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

 

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;
Super User
Posts: 6,011

Re: Merging with same ID considered distinctly

[ Edited ]

The solution from @draycut 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;

New Contributor
Posts: 4

Re: Merging with same ID considered distinctly

Posted in reply to Astounding

Thank you very much for your help! It works

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 137 views
  • 0 likes
  • 4 in conversation