Creating a table from two datasets but conditional on one dataset

[Mistletoad]

I have two datasets. The first dataset, crsp.dsf, includes daily stock returns for multiple securities. My second dataset, uniquepermnos, includes a list of securities.

I want to filter crsp.dsf for the securities in my uniquepermnos.

This is the uniquepermno dataset

	
1	10002	
2	10026	
3	10032	
4	10044	
5	10051	
6	10065	
7	10078

This is the dsfdata.

	permno date ret
1	10026	19860204	C	
2	10026	19860205	0.042553	
3	10026	19860206	-0.034014	
4	10026	19860207	-0.014085	
5	10026	19860210	0.014286	
6	10026	19860211	-0.021127	
7	10026	19860212	-0.003597	
8	10026	19860213	-0.021661	
9	10026	19860214	0.003690	
10	10026	19860218	0.044118

This is the example code below. As you can see, it is only picking up 2 permnos. I want it to pick up all permnos in the uniquepermnos dataset.

data dsfdata;

set dsfdata;
if permno=10026 or permno=10032;
run;

[Reeza · Accepted solution]

1. Merge and use the IN data set option

data want;
merge dsfdata (in=dsf) uniquepermno (in=unique);
by permno;
if unique;
run;

2. SQL 

proc sql;
create table want as
select * from dsfdata
where permno in (select permno from uniquepermno);
quit;

There are many other ways including a SQL left join, hash, formats or even a data step and temporary array.

---

@Mistletoad wrote:

I have two datasets. The first dataset, crsp.dsf, includes daily stock returns for multiple securities. My second dataset, uniquepermnos, includes a list of securities.

I want to filter crsp.dsf for the securities in my uniquepermnos.

This is the uniquepermno dataset

	
1	10002	
2	10026	
3	10032	
4	10044	
5	10051	
6	10065	
7	10078

This is the dsfdata.

	permno date ret
1	10026	19860204	C	
2	10026	19860205	0.042553	
3	10026	19860206	-0.034014	
4	10026	19860207	-0.014085	
5	10026	19860210	0.014286	
6	10026	19860211	-0.021127	
7	10026	19860212	-0.003597	
8	10026	19860213	-0.021661	
9	10026	19860214	0.003690	
10	10026	19860218	0.044118

This is the example code below. As you can see, it is only picking up 2 permnos. I want it to pick up all permnos in the uniquepermnos dataset.

data dsfdata;

set dsfdata;
if permno=10026 or permno=10032;
run;

---

[PaigeMiller]

proc sql;
    create table want as select * from dsfdata where permo in
         (select distinct permno from uniquepermno);
quit;

[Kurt_Bremser]

Hash:

data want;
set dsfdata;
if _n_ = 1
then do;
  declare hash unique (dataset:"uniquepermno");
  unique.definekey("permno");
  unique.definedone();
end;
if unique.check() = 0;
run;