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?
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?
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 🙂
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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: