topic Re: Date lookup in list of dates in SAS Programming

Date lookup in list of dates

sg_kr — Tue, 01 Sep 2020 11:11:49 GMT

Hi Team,

i have an issue here in looking for a date variable in list of date variables in sas.

i need to run a code based on condition.

%let Daily_Dm_Load_Date =%SYSFUNC(INTNX(day,%SYSFUNC(TODAY())-1,0),date9.);
%let ds_dt =%SYSFUNC(INTNX(day,%SYSFUNC(TODAY()),0),date9.);

%put &Daily_Dm_Load_Date(1Sep2020);
%put &ds_dt.;

Proc sql;

select distinct(dm_load_date) format date9. into: CS_ds_DMdates SEPARATED BY "," from DM.CS_90DAY_DETAILS;
quit;

%put &CS_ds_DMdates.(25Aug2020,31Aug2020);

%macro Appnd ;

options mprint mlogic symbolgen compress=yes;
/*delete and replacing Old data*/

%if %sysevalf( "&Daily_Dm_Load_Date "d ne &CS_ds_DMdates.) %then %do;

%put "it is not in the list";
%else %do;
%put "it is not in the list";
%end;

%mend ;

%appnd;

i want to look the "Daily_Dm_Load_Date " in "CS_ds_DMdates".

can anyone help me on this.

Re: Date lookup in list of dates

PaigeMiller — Tue, 01 Sep 2020 11:51:30 GMT

There are a number of problems here. The two I can see directly are

Do not format macro variables. Leave them unformatted. Maxim 28.
Your %if %sysevalf will fail if CS_ds_DMdates contains more than one value because it is not valid SAS syntax. Your macro variables must resolve into valid SAS code, and you won't have that.

Place this command at the start of your code

options mprint;

Then please re-run the code after you make the changes so that the macro variables are unformatted. Show us the LOG. Paste the log into the box that appears when you click on the </> icon here. DO NOT SKIP THIS STEP.

Re: Date lookup in list of dates

sg_kr — Tue, 01 Sep 2020 13:00:46 GMT

its executing without syntax error.
but logically not correct.

here is the log.

397 %let Daily_Dm_Load_Date =%SYSFUNC(INTNX(day,%SYSFUNC(TODAY())-7,0),date9.);
398 %let ds_dt =%SYSFUNC(INTNX(day,%SYSFUNC(TODAY()),0),date9.);
399 LIBNAME DM "/SASEBI/OPS/data/CS Dashboard";
NOTE: Libref DM was successfully assigned as follows:
Engine: V9
Physical Name: *****************
400 %put &Daily_Dm_Load_Date;
SYMBOLGEN: Macro variable DAILY_DM_LOAD_DATE resolves to 25AUG2020
25AUG2020
401 %put &ds_dt.;
SYMBOLGEN: Macro variable DS_DT resolves to 01SEP2020
01SEP2020
402
403 LIBNAME DM "***********************";
NOTE: Libref DM was successfully assigned as follows:
Engine: V9
Physical Name: ***************
404 Proc sql;
405 select distinct dm_load_date into: CS_ds_DMdates1 separated by ' ' from
405! DM.CS_90DAY_DETAILS;
406 quit;
NOTE: The PROCEDURE SQL printed page 18.
NOTE: PROCEDURE SQL used (Total process time):
real time 33.66 seconds
cpu time 8.19 seconds

SYMBOLGEN: Macro variable CS_DS_DMDATES1 resolves to 25AUG2020 30AUG2020
407 %put &CS_ds_DMdates1;
25AUG2020 30AUG2020
408
409 options mprint;
410 %macro Appnd ;
411 LIBNAME DM ***************;
412 options mprint mlogic symbolgen compress=yes;
413
414 /* */
415 %if ( &Daily_Dm_Load_Date ne &CS_ds_DMdates1.) %then %do;
416
417 %put "it is not in the list";
418 %end;
419
420 %else %do;
421
422 %put "it is in the list";
423 %end;
424 %mend ;
425 %appnd;
MLOGIC(APPND): Beginning execution.

MPRINT(APPND): options mprint mlogic symbolgen compress=yes;
SYMBOLGEN: Macro variable DAILY_DM_LOAD_DATE resolves to 25AUG2020
SYMBOLGEN: Macro variable CS_DS_DMDATES1 resolves to 25AUG2020 30AUG2020
MLOGIC(APPND): %IF condition ( &Daily_Dm_Load_Date ne &CS_ds_DMdates1.) is TRUE
MLOGIC(APPND): %PUT "it is not in the list"
"it is not in the list"
MLOGIC(APPND): Ending execution.
NOTE: Remote submit to GRID complete.
NOTE: Remote submit to SSASGRS complete.

Re: Date lookup in list of dates

ballardw — Tue, 01 Sep 2020 16:47:04 GMT

@sg_kr wrote:

its executing without syntax error.
but logically not correct.

here is the log.

397 %let Daily_Dm_Load_Date =%SYSFUNC(INTNX(day,%SYSFUNC(TODAY())-7,0),date9.);
398 %let ds_dt =%SYSFUNC(INTNX(day,%SYSFUNC(TODAY()),0),date9.);
399 LIBNAME DM "/SASEBI/OPS/data/CS Dashboard";
NOTE: Libref DM was successfully assigned as follows:
Engine: V9
Physical Name: *****************
400 %put &Daily_Dm_Load_Date;
SYMBOLGEN: Macro variable DAILY_DM_LOAD_DATE resolves to 25AUG2020
25AUG2020
401 %put &ds_dt.;
SYMBOLGEN: Macro variable DS_DT resolves to 01SEP2020
01SEP2020
402
403 LIBNAME DM "***********************";
NOTE: Libref DM was successfully assigned as follows:
Engine: V9
Physical Name: ***************
404 Proc sql;
405 select distinct dm_load_date into: CS_ds_DMdates1 separated by ' ' from
405! DM.CS_90DAY_DETAILS;
406 quit;
NOTE: The PROCEDURE SQL printed page 18.
NOTE: PROCEDURE SQL used (Total process time):
real time 33.66 seconds
cpu time 8.19 seconds

SYMBOLGEN: Macro variable CS_DS_DMDATES1 resolves to 25AUG2020 30AUG2020
407 %put &CS_ds_DMdates1;
25AUG2020 30AUG2020
408
409 options mprint;
410 %macro Appnd ;
411 LIBNAME DM ***************;
412 options mprint mlogic symbolgen compress=yes;
413
414 /* */
415 %if ( &Daily_Dm_Load_Date ne &CS_ds_DMdates1.) %then %do;
416
417 %put "it is not in the list";
418 %end;
419
420 %else %do;
421
422 %put "it is in the list";
423 %end;
424 %mend ;
425 %appnd;
MLOGIC(APPND): Beginning execution.

MPRINT(APPND): options mprint mlogic symbolgen compress=yes;
SYMBOLGEN: Macro variable DAILY_DM_LOAD_DATE resolves to 25AUG2020
SYMBOLGEN: Macro variable CS_DS_DMDATES1 resolves to 25AUG2020 30AUG2020
MLOGIC(APPND): %IF condition ( &Daily_Dm_Load_Date ne &CS_ds_DMdates1.) is TRUE
MLOGIC(APPND): %PUT "it is not in the list"
"it is not in the list"
MLOGIC(APPND): Ending execution.
NOTE: Remote submit to GRID complete.
NOTE: Remote submit to SSASGRS complete.

I would suspect that the "logically not correct" is coming from your variable CS_DS_DMDATES1 .

Note this line in your log:

SYMBOLGEN: Macro variable CS_DS_DMDATES1 resolves to 25AUG2020 30AUG2020

So CS_DS_DMDATES1 contains a string that looks like 2 dates. Which is NOT equal to the variable DAILY_DM_LOAD_DATE which contains a single value of 25AUG2020 . So 25AUG2020 is not equal to 25AUG2020 30AUG2020. If you want to compare a single value to multiple then you would use an IN comparison or parse the second list of values one at a time.

Re: Date lookup in list of dates

PaigeMiller — Tue, 01 Sep 2020 17:26:02 GMT

This problem is the problem I was referring to in my comment #2 above. This line

%if ( &Daily_Dm_Load_Date ne &CS_ds_DMdates1.) %then %do;

resolves to

%if ( 25AUG2020 ne 25AUG2020 30AUG2020) %then %do;

Can you see why this won't work? Do you see the syntax error here? A fundamental principle of writing macros is that when the macro variable is replaced by its value, it must produce working SAS code, and that didn't happen here. Can you re-write this statement without macro varialbes so that it is valid SAS code? If so, then you can drop macro variable in and it has a much better chance of working.

Also, you are still formatting your macro variables, this is not a good thing to do, leave them unformatted.