topic Re: Dataset - Require a variable to occur at least n times in SAS Programming

Dataset - Require a variable to occur at least n times

adamlvkvist — Fri, 05 Feb 2021 10:21:31 GMT

I have a dataset of bond transactions and want each bond to occur at least 30 times during my time range. There is one variable that is unique for each bond (an CUSIP Id) that I should be able use to see if the bond occurs n times. In other words, from my (full) dataset I would like to create a new dataset that only contains bonds that trades n times. Additionally, from this new dataset, I would like to calculate the average daily bond price. Thus, I need to see how many times an bond is traded during one day, compute average price and create a new dataset with the average prices and only one "trade" per day.

Could anyone please help a beginner?

Re: Dataset - Require a variable to occur at least n times

PeterClemmensen — Fri, 05 Feb 2021 11:14:24 GMT

Do you want two new data sets or one?

There should be 30 occurences of CUSIP in the entire time range and not within each trade date, correct?

Re: Dataset - Require a variable to occur at least n times

adamlvkvist — Fri, 05 Feb 2021 11:18:49 GMT

Two; the first reduces the full data set so each CUSIP occurs at least 30 times during the time range, the second data set reduces the first further so each CUSIP only "trades" once a day and the price is the average daily price.

There should be 30 occurences of CUSIP in the entire time range and not within each trade date, correct? - Exactly! 🙂

Re: Dataset - Require a variable to occur at least n times

PeterClemmensen — Fri, 05 Feb 2021 11:42:47 GMT

Ok. See if you can use this as a template. Since I don't have your data, I use SASHELP.STOCKS to create some example data. I create it so IBM, Microsoft are traded more than Intel.

data have;
   set sashelp.stocks;
   if stock in ('IBM', 'Microsoft') then do;
      output; output;
   end;
   else output;
run;

proc freq data = have;
   tables stock / nocum nopercent;
run;

So in this example, I want at least 250 obs for a stock to include in the first data set (change this to 30)

proc sql;
   create table one as
   select * from have
   group by stock
   having n(stock) > 250;
quit;

Next, I use this to create averages for each date.

proc summary data = one nway;
   class date;
   var close;
   output out = want(drop = _:) mean =;
run;

Hope this helps 🙂