Pass a Calculated Date Variable into a PROC SQL Statement as a Criteria

[Kody_devl]

I have a PROC SQL Statement that works fine if you  run the query "Today" (When the last system file date in the warehouse and Today's system date are the same.

PROC SQL;

CREATE TABLE WORK.Roll AS

SELECT

t1.Loan_Number

FROM WORK.LOAN_DETAIL_STATUS t1

WHERE t1.Last_Stat = '*' and t1.Out_SYS_date <> Today();

*********************************************************************************

If I want to run the code on 11/14/2016 (=Today) and the last file date is 11/10/2016 (=Work.Last_File.Last_File_Date = 11/10/2016 (This table and field hold this value))

NOTE: Today() - Last_File_Date = 4 (days) (This Where clause works (below))

WHERE t1.Last_Stat = '*' and t1.Out_SYS_date <> Today()-4;

How can I set a variable (something like this)=

%Let Adj_Days = Today()- Last_File_Date

and then

WHERE t1.Last_Stat = '*' and t1.Out_SYS_date <> Today()- &Adj_Days.;

I have tried this but can't get a variable = to the correct calculated date, the last_file_date to run correctly.

[LaurieF]

I would rethink the problem a little. Since you already no the last file date, you can put that into a constant and pass that into the table creation:

proc sql noprint; /* Stops the output window triggering */
select last_file_date
   into: last_file_date
   from last_file;

create table roll as
   select loan_number
      from loan_detail_status
    where last_stat = '*' and out_sys_date ne &last_file_date; /* Use 'ne' or '^=' in preference to '<>' */
quit;

Otherwise, you can refer to today() within a %sysfunc call:

%let today = %sysfunc(today());

But you still need to get last_file_date out of the last_file dataset somehow. You could jump through hoops doing a subquery and bypass last_file altogether, but it would depend on the size of loan_detail_status. If it's small, it wouldn't make any difference.

Using EG makes the query generation a little complicated of course - and I don't have EG to hand to check exactly how that would work. But you could put that first select in your pre-code.

[Kody_devl]

Hi Laurie,

I love your suggestion (I am not a SAS developer so I am learning). Thank you.

I adopted the complete query to my problem and it runs without error BUT is failing to remove the 11/14/2016 Out SYS Files (t1.out_sys_date ne &L_SYS_File.)

All source formats are MMDDYY10 yet I have a feeling that somehow, the formats aren't matching and that is why it misses the 11/14/2016.

IF I use this format, the query works:

WHERE t1.Out_Sys_Date NE '14Nov2016'd AND t1.Last_Stat = '*';

Do you have any idea of what I might be missing?

Thank you.

Kody_Devl

Here is what I have:

proc sql noprint; /* Stops the output window triggering */

select L_SYS_File

into: L_SYS_File

from LOAN_DETAIL_SRCES_F_L;

%Put &L_SYS_File.; /* Produces the correct date in the correct date formatted as '11/14/2016' */

CREATE TABLE WORK.Roll_3 AS

SELECT

3 as PostNo,

t1.Loan_Number,

t1.Out_Status_Day AS Day format mmddyy8.,

t2.SourceDate_dte_NEXT AS Posting_Day format mmddyy8.,

"3 OUT" as Type,

t1.StatusNo as StatNo,

t1.Status as Stat,

/* cnt */

-(COUNT(t1.Loan_Number)) FORMAT=CHAR10. AS cnt

FROM WORK.LOAN_DETAIL_STATUS t1

INNER JOIN WORK.LOAN_DETAIL_SRCES t2 ON (t1.Out_Status_Day = t2.Source_Date_dte)

where t1.last_stat = '*' and t1.out_sys_date ne &L_SYS_File.

GROUP BY

t1.Loan_Number,

t1.Out_Status_Day,

t2.SourceDate_dte_NEXT,

t1.StatusNo,

t1.Status;

QUIT;

[SuryaKiran]

You have to make sure that L_SYS_File and Out_Sys_Date are both in same format. Your getting  L_SYS_File from table LOAN_DETAIL_SRCES_F_L. Check for the formt in table LOAN_DETAIL_SRCES_F_L. You said

%Put &L_SYS_File.; /* Produces the correct date in the correct date formatted as '11/14/2016' */

'11/14/2016' --> Does this show with quotes. If so then it may be in character format.

If you see it as date SAS may see it as character. For example

data one;

infile datalines;

input date $10. ;

datalines;

10/10/2016

;

run;

In this you can see the date as output but the format is not mmddyy10. it has format $10.

If L_SYS_File is as character in LOAN_DETAIL_SRCES_F_L table the use INPUT

proc sql noprint; /* Stops the output window triggering */

select INPUT(L_SYS_File,mmddyy10.) as L_SYS_File

into: L_SYS_File

from LOAN_DETAIL_SRCES_F_L;

[Kody_devl]

Hi SuryaKiran

The two Data files are both Date MMDDYY10.  I use Properties/Column to view the properties of these fields in their respective tables.  Also, if I used your "input" format code, it says that it needs a character input format to be able to convert (so I am sure  t1.out_sys_date field is a "date mmddyy10" format.)

This Works:

where t1.last_stat = '*' and t1.out_sys_date NE '14Nov2016'd

This Doesn't Work:

where t1.last_stat = '*' and ne &L_SYS_File.

It seems like the answer would be in this comparison.

Should I convert the value of the variable to this '14Nov2016'd format from mmddyy10 which is currently outputing as 11/14/2016 (log does not show this '11/14/2016', it shows 11/14/2016).

If I should, do don't know how to do this?

Thanks for you thoughts.

[Oligolas · Accepted solution]

Hi,

try this:

where t1.last_stat = '*' and put(t1.out_sys_date,mmddyy10.) NE "&L_SYS_File."

Cheers

[Kody_devl]

Oligolas,

The good news is that is works. 

The bad news is that I am not exactly sure why so I still can't fish all by my self.

I thought that t.out_sys_date is already in the correct (mmddyy10) format.  Isn't this "put" statement forcing the date back into the same

format?  It kinda doesn't make sense to me but, you obvisouly understand what is happening here.  Could you please explain why this works?

Thanks agian.

Kody_Devl

[SuryaKiran]

If the code worked perfect then L_SYS_File is character. Because PUT() converts numeric to character.
If character=character it makes sense and if it is character = numeric it dosen't make sense.

[Kody_devl]

SuryaKiran

Thank you!

I struggle with SAS formatting as I clearly saw in the table/Column, it said "date / mmddyy10". 

So, it wouldn't make sense to me, why a '14Nov2016'd date format would work in the query of that table.

I wish it was more, WYSWIG, and old acronym, as I am old, "What you see is what you get".

Thanks again.

Kody_Devl

[Oligolas]

Hi,

the put statement converts the out_sys_date value to a character value having the same format as your L_SYS_File variable.

put(t1.out_sys_date,mmddyy10.)

The L_SYS_File macro variable is a character value you have to quote in order to performing the comparison with the put value.

where t1.last_stat = '*' and put(t1.out_sys_date,mmddyy10.) NE "&L_SYS_File."

[SuryaKiran]

The way it worked is not how you guessed. L_SYS_File  is not character, it  is in the format mmddyy10. Putting quotes made it to campare as character.

For example:

data one;

infile datalines;

format L_SYS_File mmddyy10.;

input L_SYS_File mmddyy10. ;

datalines;

10/10/2016

;

run;

　

proc sql;

select L_SYS_File format=mmddyy10. into: L_SYS_File

from one;

quit;

data two;

set one;

comparing_as="&L_SYS_File ";

run;

Like the above code in

where t1.last_stat = '*' and put(t1.out_sys_date,mmddyy10.) NE "&L_SYS_File."

Here L_SYS_File is numaric and putting quotes treats it as character and compares with put(t1.out_sys_date,mmddyy10.).

[LaurieF]

Dear Kody, That's what I get with posting code without running it locally to check that it works. Sorry.

select last_file_date format=5. 
   into: last_file_date 
   from last_file;

This forces the value into its internal format (days since 1Jan1960), instead of its external representation. With a length of 5, that will work up until 15/10/2233. <grin>

Laurie

[Tommywhosc]

Hi Kody_Devl,

I'm really surprised that SuryaKiran's code didn't work.

If you want to try making a macro variable  of the form   '14Nov2016'd     give this a try:

proc sql noprint; /* Stops the output window triggering */

select "'"||put(L_SYS_FILE,date9.)||"'d"

into: L_SYS_File

from LOAN_DETAIL_SRCES_F_L;