CODEWITHSUNDEEP
sasproc-sql
Yuri
Yuri

Reputation: 3

INTNX in PROC SQL

I'm relatively new to SAS, so I'm sorry if I'm being kind of naive or silly with this question.

I'm trying to link the ending date to the initial date on my "where" clause through INTNX function, but the program returns results with a different date. I really do not know what I'm doing wrong here. I'm using EG 8.2.

%LET ini_date = '10DEC2022'd; %LET end_date = intnx('week',&inidate, 1);

PROC SQL; CREATE TABLE FAT_CRED AS SELECT * FROM table A WHERE A.DAT_MOV >= &ini_date. AND A.DAT_MOV < &end_date. GROUP BY 1,2,3,4 ;QUIT;

Strangely, the system returns end_date as Feb 3, 2020 (03FEB2020:00:00:00.000000), so I don't get any data from my query. I was expecting to gather the info between 10DEC and 16DEC, but I couldn't grasp if the problem lies in the compatibility between PROC SQL and the INTNX function (and its syntax), the date format in my table A, etc.

Thanks in advance for any help!

Upvotes: 0

Views: 2974

Answers (2)

Tom
Tom

Reputation: 51621

The most likely cause of your confusion is that your variable appears to contain datetime values (you show a value of '03FEB2020:00:00:00.000000') and not actual date values. SAS stores date values as days and datetime values as seconds. So any reasonable date value is going to be some time in the early morning of January 1, 1960 if treated as a datetime value.

Either convert the datetime value to a date value before making the comparison

WHERE datepart(A.DAT_MOV) >= &ini_date.
  AND datepart(A.DAT_MOV) < &end_date.

Or generate datetime values into your macro variables.

%LET ini_date = '10DEC2022:00:00:00'dt; 
%LET end_date = "%sysfunc(intnx(dtweek,&ini_date, 1),datetime19.)"dt;

If using "17DEC2022"d works but using 22996 or intnx('day','10DEC2022'd,7) fails then you should use macro code to generate a date literal instead of just a date value. You can use the %SYSFUNC() macro function call to invoke the INTNX() SAS function in your macro code.

%LET end_date = "%sysfunc(intnx(day,&ini_date, 7),date9.)"d;

Upvotes: 0

Yuri
Yuri

Reputation: 3

Update: I've found that I was using the wrong arguments in INTNX function. I needed seven days after my ini_date, so the correct code is %LET END_DATE = INTNX('DAY',&INI_DATE,7);

After that, I run the PROC SQL part, but the program doesn't recognize end_date as I wish, because it reads it as integer (22996). How can I convert this to date in order to use it in the WHERE clause? please note: (1) the query runs fine when a use '17DEC2022'd in the ending date (2) I start PROC SQL right after I declare ini_date and end_date with %LET.

Thanks!

Upvotes: 0

Related Questions