topic tabulating dates in SAS Procedures

tabulating dates

Yennie — Thu, 22 Apr 2010 03:07:04 GMT

hey all,

i have a dataset name risks_data - that contains fromdate, todate and risks. fromdate and todate in the format of date9. and risks number. as below.

fromdate todate risks
02MAR2009 02MAR2010 1
21FEB2010 21FEB2011 1
.
.
.

and i am need to find the number of risks in three different months - 1st Jan 2010, 1st Feb 2010, 1st March 2010 so I had the below macro codes.

%macro date;
Data date_&month;
set risks_data ( keep = fromdate todate risks);
Where fromdate <= "%sysfunc(putn(%sysfunc(intnx(Month,%sysfunc(date()),-1)),EURDFDE9.))"D <= todate and risks;
run;
%mend date;

once thats done, I need to tabulate the number of risks into a table showing something as below (this is where I am not sure how to go about doing it)

Effective_Day Risks
01Jan2010 2311
01Feb2010 1452
01March2010 2785

hope someone can help me out on this one!!

thanks in advance!
Yennie

Re: tabulating dates

Yennie — Thu, 22 Apr 2010 03:08:08 GMT

continue.....
le "%sysfunc(putn(%sysfunc(intnx(Month,%sysfunc(date()),-1)),EURDFDE9.))"D le todate and risks;
run;
%mend date;

once thats done, I need to tabulate the number of risks into a table showing something as below (this is where I am not sure how to go about doing it)

Effective_Day Risks
01Jan2010 2311
01Feb2010 1452
01March2010 2785

hope someone can help me out on this one!!

thanks in advance!
Yennie

Re: tabulating dates

Patrick — Thu, 22 Apr 2010 09:36:09 GMT

Hi Yennie

Some questions and ideas.

Q: are the values for fromdate and todate really strings or are these SAS date values with a SAS date format assigned? You can easily check this by running a PROC CONTENTS.

Q: Your example data shows the same dates for fromdate and todate. Is this how your data really looks like? And would that mean that these 2 cases wouldn't be counted anywhere?

Q: What happens if a risk period (fromdate to todate) goes over a monthend. Should this risk be counted in 2 months? Or only when it started?

Forget about using any macro code. It won't be needed.

What most likely has to be done is creating some additional observations (i.e. one obs per month where the risk exists) and then a PROC TABULATE with an appropriate format applied to the variable containing the date.

Have also a look at functions like INTCK() and INTNX().

Please provide also some "real" sample data, best in form of a data step creating a SAS table.

HTH
Patrick Message was edited by: Patrick