Creating a data table from date intervals

[mjheever]

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!

[BrunoMueller · Accepted solution]

Hi

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;

[mjheever]

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.

[mjheever]

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

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