Thank you all for your solutions! They all accomplished what I was trying to do, this one is just the simplest for me to understand and update down the road. Here is the solution that I ended up using, which was a combination from all 3 submitted solutions: /*Create a dataset which is a listing of all GVKEYs with a filing on or after 4/15/2020*/
data gvkey_apr152020; set y1;
where apr152020=1;
keep gvkey conm tic apr152020;
run;
/*Drop duplicates*/
proc sort data=gvkey_apr152020 nodupkey dupout=dups2;
by gvkey;
run;
/*Create the table that makes dummy variables for each tax year*/
/*This exports the file to excel. We will re-import in the next step */
/*This is done to convert the table to a dataset*/
ods excel file="NOL_CB_entitylistingA.xlsx";
proc tabulate data=y1;
class gvkey taxyr;
table gvkey,
taxyr*n=' '
/misstext=' '
;
run;
ods excel close;
******************************************************************;
** update headers and un-merge cells manually in excel **;
******************************************************************;
/*Re-import the excel table*/
proc import
datafile="NOL_CB_entitylistingA.xlsx"
out=GVKEY_taxyr;
scantext=yes;
run;
/*Merge A) the list of GVKEYs with filings after 4/15/2020 with */
/* B) the dataset with dummy variables for filings in each tax year */
proc sql;
create table perm.CARESNOLCB_sample
as select *
from gvkey_apr152020 a left join GVKEY_taxyr b
on a.gvkey = b.gvkey
order by gvkey;
quit;
/*Export to excel*/
proc export
data=perm.CARESNOLCB_sample
dbms=xlsx
outfile="CARESNOLCB_sample.xlsx"
replace;
run;
... View more