Merging with same ID considered distinctly

[Fcot]

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,

[RM6]

try using merge by target permno

[PeterClemmensen · Accepted solution]

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;

[Astounding]

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;

[Fcot]

Thank you very much for your help! It works