topic Re: How do I create a calendar table in proc sql? in SAS Programming

How do I create a calendar table in proc sql?

seohyeonjeong — Wed, 24 Nov 2021 06:20:01 GMT

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.

Re: How do I create a calendar table in proc sql?

ChrisNZ — Wed, 24 Nov 2021 06:28:43 GMT

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

Re: How do I create a calendar table in proc sql?

andreas_lds — Wed, 24 Nov 2021 07:26:37 GMT

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;

Re: How do I create a calendar table in proc sql?

Ksharp — Wed, 24 Nov 2021 11:48:12 GMT

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 )

Re: How do I create a calendar table in proc sql?

seohyeonjeong — Thu, 25 Nov 2021 05:48:28 GMT

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!:)

Re: How do I create a calendar table in proc sql?

andreas_lds — Thu, 25 Nov 2021 07:08:12 GMT

@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?

Re: How do I create a calendar table in proc sql?

ChrisNZ — Thu, 25 Nov 2021 07:13:23 GMT

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

Re: How do I create a calendar table in proc sql?

ChrisNZ — Thu, 25 Nov 2021 07:16:59 GMT

> 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. 🙂

Re: How do I create a calendar table in proc sql?

Ksharp — Thu, 25 Nov 2021 12:23:53 GMT

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 .

Re: How do I create a calendar table in proc sql?

ChrisNZ — Fri, 26 Nov 2021 22:15:43 GMT