topic Re: Creating a data table from date intervals in SAS Procedures

Creating a data table from date intervals

mjheever — Tue, 18 Feb 2014 16:53:25 GMT

Hi guys,

I got stuck with this one assignment, so I was hoping someone could help me out.

Question 1 asks that we must create a table that must consist of the following fields (I have included a description of the fields) :

Field	Description	Format
Key	Unique Key for that Date	Numeric
Date	Date9.	date9.
date_description	Long Description of the date	Character
RSA_WorkdayInd	Whether it is a working day. Assume no public holidays	Numeric
Fin_Year	FNB Financial Year (1Jul - 30 June)	Numeric
WeekendInd	Whether it is a weekend	Numeric
DayOfWeek	The Day of the Week	Character
Calendar_Month	Calendar Month YYYYMM	Numeric
Calendar_Day_of_Month	Day in th Month	Numeric
Calendar_Day_of_Year	Day in the Year	Numeric
Calendar_Week_Number	Week Number in the Year	Numeric
Calendar_Month_Number	Calendar Month Number	Numeric
Calendar_Month_name	Month Name	Character
Calendar_Quarter	Calendar Quarter	Character
Calendar_Year	Calender Year	Numeric
RSA_WorkingTimeMinutes	Assume 8 working hours.	Numeric

In this table you should have every single day from 01Jan2010 – 31 DEC2014 ie you need to generate a record for each day.

I have tried the following, but I'm not sure how to include the months and days, since in the code below it generates the wrong output- can you suggest an easier way to continue with this process ?,

data date_dataset;

begin='01JAN2010'd;

end='31DEC2014'd;

i=0;

do year=year(begin) to year(end);

do month=month((begin+i)) to month((begin+i+1));

output;

end;

i=i+1;

end;

run;

Thank you!

Re: Creating a data table from date intervals

BrunoMueller — Tue, 18 Feb 2014 17:14:02 GMT

Have a look at this sample code, it should give you a starting point. There are several functions that return information from a date like DAY, YEAR, MONTH, QTR etc. You can also use the PUT function together with a date format to get your char values

%let fromDate = 01Jan2010;
%let toDate = 31DEC2014;
data want;
  length key 8; 
  do date = "&fromDate"d to "&toDate"d; 
    key + 1; 
    RSA_WorkdayInd = ( 2 <= weekday(date) <= 6);
    WeekendInd = (not RSA_WorkdayInd);
    Calendar_Week_Number = week(date, "V");
    DayOfWeek = put(date, downame3.);
    Fin_Year = year(intnx("year.7", date, 0));
    output; 
  end; 

  format 
    date date9.
  ;
run; 

Re: Creating a data table from date intervals

mjheever — Tue, 18 Feb 2014 17:19:24 GMT

Thank you Bruno.

I will try this code out and also apply a similar approach to the rest of the fields.

I will give an update on the result once I've applied it to all the fields.

Re: Creating a data table from date intervals

mjheever — Wed, 19 Feb 2014 13:07:02 GMT

Here is the output Bruno- it worked like a charm.

Thank you once again :smileygrin: :smileygrin: :smileygrin: :smileygrin: :smileygrin: !