Solved
Contributor
Posts: 42

# 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: 825

## 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;

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

## 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

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
• 4495 views
• 6 likes
• 2 in conversation