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;
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;
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;
proc sql;
create table want as select * from dsfdata where permo in
(select distinct permno from uniquepermno);
quit;
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: