topic Re: Selecting all groups fitting criteria in SAS Data Management

Selecting all groups fitting criteria

FinRes — Tue, 27 Aug 2019 19:15:47 GMT

I am a relatively novice SAS user doing finance research.

I am working on a large database of historical data for a number of securities each with a unique CUSIP. I want to select all the securities whose initial offering date is after some specified date, and which were still listed as of a certain date. However I want to include all available data from before and after those dates for those securities. I am using the existence of an end of day price for the first date of my range and last date of my range.

So for example, I have daily price data for 5 securities. I want all securities which were listed prior to Jan 1, 2010 and were still listed as of Dec 31, 2014. Securities 1, 3 and 4 meet this criteria but security 2 was initially offered on May 15, 2012 and security 5 was only has price data through October 5, 2013, implying that it is now delisted/defunct. I want some way to get the data for just securities 1, 3 and 4, but including all available data prior to Jan 1, 2010 and after Dec 31, 2014 for those securities. Deleting the data for the CUSIPs I don't want would also work just as well.

I have tried a number of solutions on this forum that were used for similar problems (like this one) but none of them have worked.

Re: Selecting all groups fitting criteria

PeterClemmensen — Tue, 27 Aug 2019 19:17:29 GMT

Welcome to the SAS Community 🙂

Can you provide some example of what your data looks like? Makes it much easier to provide a usable code answer 🙂

Re: Selecting all groups fitting criteria

PeterClemmensen — Tue, 27 Aug 2019 19:28:46 GMT

Take a look at the data below (I just made something up).. Only Security 1, 2, and 3 fulfill the criteria that you set up. Security 4 and 5 do not. Hope you can use the code 🙂

data have;
	do Security=1, 2, 3;
		do date='01jul2009'd to '01jul2015'd;
			Price=rand('integer', 100, 1000);
			output;
		end;
	end;

	Security=4;
	do date='01jul2009'd to '05oct2009'd;
		Price=rand('integer', 100, 1000);
		output;
	end;

	Security=5;
	do date='01jul2010'd to '01jul2015'd;
		Price=rand('integer', 100, 1000);
		output;
	end;

	format date date9.;
run;

data want;
	if _N_=1 then do;
		declare hash h1(dataset:"have(where=(date lt '01jan2010'd))");
		h1.definekey("Security");
		h1.definedone();
		declare hash h2(dataset:"have(where=(date gt '31dec2014'd))");
		h2.definekey("Security");
		h2.definedone();	
	end;

	set have;

	if h1.check()=0 & h2.check()=0;
run;

Re: Selecting all groups fitting criteria

PeterClemmensen — Tue, 27 Aug 2019 19:32:41 GMT

Alternatively, using PROC SQL

proc sql;
	create table want as
	select * from have
	group by Security
	having sum(date lt '01jan2010'd) & sum(date gt '31dec2014'd)
	order by Security, date;
quit;

Re: Selecting all groups fitting criteria

FinRes — Wed, 28 Aug 2019 01:37:14 GMT

Thanks! This worked perfectly.

I did not try the other solution since I am a little more familiar with SQL, though I might try the other solution later too.

Re: Selecting all groups fitting criteria

PeterClemmensen — Wed, 28 Aug 2019 06:30:18 GMT

I'm just glad you found your answer 🙂