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

[WaiLoon]

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

[PaigeMiller]

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.

[WaiLoon]

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

[PaigeMiller]

---

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

[Kurt_Bremser]

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.

[WaiLoon]

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? 

[Patrick]

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

[Kurt_Bremser · Accepted solution]

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

[WaiLoon]

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

[Reeza]

---

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

[Kurt_Bremser]

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)