How do I create a calendar table in proc sql?

[seohyeonjeong]

Hello

I'm a beginner at SAS programming.

I have to create a date-master table a.k.a. calendar table in proc SQL.

(Every code should write in proc SQL in SAS.)

For example, the date should be in the format yyyymm.

The start date is fixed like 201810. 

The end date should be the last month of the current year like 202112.

(I hope to use the function;today() etc)

The output should be

Date       Year     Month 

201810    2018    10

201811    2018    11

201812    2019    12

201901    2019    01

...

If I could get any help, I will really appreciate it.

Thanks.

[ChrisNZ]

>Every code should write in proc SQL

SQL is a really limited and poor language compared to SAS's.

This limitation means many things will be much harder or near impossible. 

A five-line SAS code might necessitate hundreds of SQL code lines to do the same job.

[andreas_lds]

This task could be solved in data step easily, but i don't see a solution using proc sql. Please explain why you want proc sql.

Here's a data step:

data work.calendar;
   StartDate = '1Oct2018'd;
   EndDate = mdy(12, 1, year(today()));
   Date = StartDate;

   do while(Date < EndDate);
      Year = year(Date);
      Month = month(Date);
      output;
      Date = intnx('month', date, 1, 'B');
   end;

   format Date yymmn6.;
   drop StartDate EndDate;
run;

[Ksharp · Accepted solution]

You can not  get it by pure SQL statement in SAS.

%macro calendar_table(start_date=  );
%let start=%sysfunc(inputn(&start_date.,date9.)); 
%let end=%sysfunc(intnx(year,%sysfunc(date()),0,e));
%let n_month=%sysfunc(intck(month,&start.,&end.));

proc sql;
create table want(date num format yymmn6.,year num,month num);
insert into want 
%do i=0 %to &n_month.;
 %let date=%sysfunc(intnx(month,&start.,&i.));
 %let year=%sysfunc(year(&date.));
 %let month=%sysfunc(month(&date.));
 values(&date.,&year.,&month.) 
%end;
;
quit;
%mend;


%calendar_table(start_date= 01oct2018 )

[seohyeonjeong]

Truly it is.

But thank you for making this code. 

We are going to change almost every SAS code to SQL language in the near future, so I have to change every code by using PROC SQL as possible as I can. Thank you anyway!:) 

[andreas_lds]

---

@seohyeonjeong wrote:
...

We are going to change almost every SAS code to SQL language in the near future,

...

---

Why? To increase memory usage? To make code less readable?

[ChrisNZ]

To multiply the number of lines of code, a sure measure of code quality and coder productivity.

[ChrisNZ]

> You can not  get it by pure SQL statement in SAS.

One cold argue this is pure SQL. The macro language just writes the SQL code instead of the developer.

If only SQL should used, them the macro language is off limits right? 

Come on OP, you know you want to write all that endless SQL code. 🙂

[Ksharp]

Yes.But it mixed with Macro which is not supposed to there.


"Come on OP, you know you want to write all that endless SQL code. "
Chris, You are too rascal .

[ChrisNZ]