CODEWITHSUNDEEP
sas
63549
63549

Reputation: 95

Wrong date output

I am trying to convert simple date to date9. format.

%let snapshot_date=201806;
%let dt0=%sysfunc(intnx(month,%sysfunc(inputn(&snapshot_date.,yymmn6.)),0,b),yymmn6.);

data new;
set sample;
format cutoff_date date9.;
cutoff_date=input(&dt0.,anydtdte11.);
run;

I am getting cutof_date as 28jun2020 instead of 30jun2018. Is iam doing anything wrong here.

Upvotes: 0

Views: 589

Answers (2)

Tom
Tom

Reputation: 51621

So the macro statements start with a YYYMM string. Convert it to the first day of the month using INPUTN() function. Then convert it from that date back to exact same date using INTNX() function with an interval of zero. (Perhaps in your real problem the interval is not zero?). Then convert it back to a new YYYYMM string.

The SAS code you are generating is :

cutoff_date=input(201806,anydtdte11.);

That is trying to convert the number 201,806 into a date using the ANYDTDTE11. informat. Since the INPUT() function needs a string and not a number as its input SAS will convert the number 201,806 into a string using the BEST12. format. So it runs this code:

cutoff_date=input("      201806",anydtdte11.);

The ANYDTDTE informat has to decides to map those 6 characters into month, day and year so it splits into three parts 20 18 06. Since the first two are larger than 12 one must be day and the other year. It decides it is Y/D/M order. Not sure why as I have never seen that order used in real life.

Instead use the same informat in the SAS code that was used in the macro code. So to convert the string 201806 in SAS code you would use either of these statements:

cutoff_date=input("201806",yymmn6.);
cutoff_date=inputn("201806","yymmn6.");

To generate that from your macro variable you need to add the quotes. So use:

cutoff_date=input("&dt0.",yymmn6.);

Upvotes: 1

Stu Sztukowski
Stu Sztukowski

Reputation: 12909

SAS interprets dates as the number of days since Jan 1st 1960, and you are supplying a number to the input function which is designed to convert characters to numbers. anydtdte. is interpreting it incorrectly as a result. Put quotes around &dt0 and use the yymmn6. informat instead so that SAS converts it to a date correctly.

data new;
    format cutoff_date date9.;
    cutoff_date=input("&dt0.",yymmn6.);
run;

Output:

cutoff_date
01JUN2018

anydtdte. will not work here since yymmn6. is not in the list of formats it tries to read. A list of the date types it will read is located here:

https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/leforinforref/n04jh1fkv5c8zan14fhqcby7jsu4.htm?homeOnFail

Upvotes: 0

Related Questions