topic Re: How to get last day of previous month and first day for current for Decommission and Commission in SAS Programming

How to get last day of previous month and first day for current for Decommission and Commission

WaiLoon — Tue, 13 Sep 2022 12:23:25 GMT

Equipment Data
Voltage
Master Project
AIPM workflow

Hoza by BA level
User access by State/Hoza/BA

GIS Data

Hi all

My program getting hardware list from another system, but the system don't have CommissionDate and DecommissionDate. Source system is in Excel and Target is PostgreSQL.

Therefore I need to generate these 2 value every month when ETL the hardware list.

So, what I planned to do is, if the hardware from source DB not in the target DB, then I will insert and set the CommissionDate to 1st of current month.

If the hardware in target DB not in source DB, then I will set the DecommissionDate to last day of previous month.

I tried many way but unfortunately can't get it work. I can't even get the previous month work.

But today I tried this
%let Decomm=%sysfunc(intnx(month,%sysfunc(today()),-1,same), yymmdd8.);
%put &Decomm;

And I get
276 %let Decomm=%sysfunc(intnx(month,%sysfunc(today()),-1,same), yymmdd8.);
277 %put &Decomm;
SYMBOLGEN: Macro variable DECOMM resolves to 22-08-13
22-08-13

it seems like is I manage to get previous month, but I need the day to be last day of the month and also the format should be dd/mm/yyyy.

Today I tried this syntax

%let Decomm=%sysfunc(intnx(month,%sysfunc(today()),-1,same), ddmmyyyy8.);
%put &Decomm;

and I get this

276 %let Decomm=%sysfunc(intnx(month,%sysfunc(today()),-1,same), ddmmyyyy8.);
277 %put &Decomm;
SYMBOLGEN: Macro variable DECOMM resolves to 22870
22870

Other than above, i also need to get the first day of current month...

May I know what is my script missed??

Re: How to get last day of previous month and first day for current for Decommission and Commission

PaigeMiller — Tue, 13 Sep 2022 12:49:24 GMT

INTNX has several choices for the final option. Instead of same, you can use end or beginning. That will get you the desired end of month date or beginning of month date.

Today I tried this syntax

%let Decomm=%sysfunc(intnx(month,%sysfunc(today()),-1,same), ddmmyyyy8.);
%put &Decomm;

and I get this

276 %let Decomm=%sysfunc(intnx(month,%sysfunc(today()),-1,same), ddmmyyyy8.);
277 %put &Decomm;
SYMBOLGEN: Macro variable DECOMM resolves to 22870
22870

You should see an ERROR in the log

ERROR: Format name DDMMYYYY8. not found or the width and/or decimal specified for the format used are out of range.

Don't ignore errors like this. It is telling you important information. It is telling you that you are trying to use a format name that doesn't exist. The format you should use is DDMMYY8.

Re: How to get last day of previous month and first day for current for Decommission and Commission

Kurt_Bremser — Tue, 13 Sep 2022 13:06:34 GMT

Use INTNX with correct parameters:

%let first_day_curr_month = %sysfunc(intnx(month,%sysfunc(today()),0,b));
%let last_day_prev_month = %eval(&first_day_curr_month. - 1);

If you need the values in assignments and calculations, don't bother with formatting them, see Maxim 28.

Re: How to get last day of previous month and first day for current for Decommission and Commission

WaiLoon — Tue, 13 Sep 2022 14:06:28 GMT

Thanks. But what if I need to have yyyy, how should I format??

Re: How to get last day of previous month and first day for current for Decommission and Commission

PaigeMiller — Tue, 13 Sep 2022 14:14:18 GMT

@WaiLoon wrote:
Thanks. But what if I need to have yyyy, how should I format??

Please try DDMMYY10. it will give you a four digit year.

Re: How to get last day of previous month and first day for current for Decommission and Commission

WaiLoon — Mon, 26 Sep 2022 08:35:13 GMT

Hi, thanks for suggestions, and sorry for the late reply cause suddenly got some production issue need to resolve.

I tried the suggested syntax but still not able to get correct date.

%let first_day_curr_month = %sysfunc(intnx(month,%sysfunc(today()),0,b));
%let last_day_prev_month = %eval(&first_day_curr_month. -1);

UPDATE HardwareList tgt
SET DateDecommissioned = &last_day_prev_month

WHERE NOT EXISTS (.......);

I check the data, the DateDecommissioned is 1960-01-01 06:21:28

Anything i did wrong?

Re: How to get last day of previous month and first day for current for Decommission and Commission

Patrick — Mon, 26 Sep 2022 09:36:12 GMT

@WaiLoon Not that I see that anywhere in the code you've shared BUT... SAS got DATE and DATETIME values.

A SAS Date value is the count of days since 1/1/1960, a SAS DateTime value is the count of seconds since 1/1/1960.

Most of the time if you get something like DateDecommissioned is 1960-01-01 06:21:28 it's an indication that you've used a SAS DateTime FORMAT to print a SAS Date value (=prints your count of days as if it were a count of seconds).

Re: How to get last day of previous month and first day for current for Decommission and Commission

Kurt_Bremser — Mon, 26 Sep 2022 13:26:03 GMT

So your target variable is datetime, not a date.
Change the assignment:

%let first_day_curr_month = %sysfunc(intnx(dtmonth,%sysfunc(datetime()),0,b));
%let last_day_prev_month = %eval(&first_day_curr_month. -1);

Re: How to get last day of previous month and first day for current for Decommission and Commission

WaiLoon — Thu, 29 Sep 2022 01:41:40 GMT

I finally get it works, so date and datetime having different handling... it is very different from the normal SQL...

Re: How to get last day of previous month and first day for current for Decommission and Commission

Reeza — Thu, 29 Sep 2022 01:42:55 GMT

@WaiLoon wrote:

I finally get it works, so date and datetime having different handling... it is very different from the normal SQL...

Normal SQL typically only has datetime fields.

Here's a great, but longer and in depth, reference for dates and times in SAS
https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/ta-p/424354

Re: How to get last day of previous month and first day for current for Decommission and Commission

Kurt_Bremser — Thu, 29 Sep 2022 01:51:37 GMT

There is no "normal" SQL. Different database systems handle dates and times differently.

For instance, IBM's DB/2 treats them exactly like SAS, as counts of days and counts of seconds, respectively.

(This allows to store dates in 4 bytes as default)