Help using Base SAS procedures

Creating a data table from date intervals

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

Creating a data table from date intervals

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!


Accepted Solutions
Solution
‎02-18-2014 12:14 PM
SAS Super FREQ
Posts: 708

Re: Creating a data table from date intervals

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;

View solution in original post


All Replies
Solution
‎02-18-2014 12:14 PM
SAS Super FREQ
Posts: 708

Re: Creating a data table from date intervals

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;
Contributor
Posts: 42

Re: Creating a data table from date intervals

Posted in reply to Bruno_SAS

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.

Contributor
Posts: 42

Re: Creating a data table from date intervals

Posted in reply to Bruno_SAS

OutputBruno.jpg

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

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 2932 views
  • 6 likes
  • 2 in conversation