topic Re: Getting Too Many Record Using Symput Date Macros in SAS Procedures

Getting Too Many Record Using Symput Date Macros

omega1983 — Tue, 26 Mar 2013 16:00:40 GMT

I am creating a dataset named FPD. I pass a symput macro for the first day of the last month(LMM) and first day of this month(TMM). I am supposed to get 6 records for February and 76 for March (in the dataset FPD). When I run the date range by itself (ie

WHERE LN_NXT_PMT_DUE_DT EQ &LMM.

AND LN_FST_DUE_DT EQ &LMM.

I get the correct number of records. When I combine the date ranges as in the code below I am getting about 30 records more than I should. Both the LN_NXT_PMT_DUE_DT and the LN_FST_DUE_DT must equal the beginning of the month (either February or March). When I combine the two symput macros LM and TM, some of the LN_FST_DUE_DT are out of date range. Here is the full code:

data _null_;

LM = INTNX('Month',Today(),-1,'b');/* 1st day last month jaj*/

TM = INTNX('Month',Today(),0,'b');/*1st day this month jaj */

%put &LMM &TMM;

DATA FPD;

Merge Data1.my_loan_daily_&pbd_date.

(Keep=

LN_NO

BO_BORR_FST_NM

BO_BORR_MDL_NM

BO_BORR_LAST_NM

LN_LOAN_MOD_DT

);

WHERE LN_NXT_PMT_DUE_DT EQ &LMM.

AND LN_FST_DUE_DT EQ &LMM. or

LN_NXT_PMT_DUE_DT EQ &TMM.

AND LN_FST_DUE_DT EQ &TMM.

AND substr(LN_NO, 1, 6) NE '010790'

AND LN_1ST_PRIN_BA >0

AND LN_LOAN_MOD_DT EQ .

AND upcase(substr(IV_ID, 1, 1)) NE 'A';

BY LN_NO;

DROP IV_ID

LN_LOAN_MOD_DT

;

Run;

Re: Getting Too Many Record Using Symput Date Macros

Reeza — Tue, 26 Mar 2013 16:10:32 GMT

Add some brackets into your where clause to separate your AND from your OR.

I'm guessing you want the following:

WHERE (LN_NXT_PMT_DUE_DT EQ &LMM.

AND LN_FST_DUE_DT EQ &LMM. or

LN_NXT_PMT_DUE_DT EQ &TMM.

AND LN_FST_DUE_DT EQ &TMM. )

AND substr(LN_NO, 1, 6) NE '010790'

AND LN_1ST_PRIN_BA >0

AND LN_LOAN_MOD_DT EQ .

AND upcase(substr(IV_ID, 1, 1)) NE 'A';

Re: Getting Too Many Record Using Symput Date Macros

omega1983 — Tue, 26 Mar 2013 17:03:38 GMT

Thanks

Re: Getting Too Many Record Using Symput Date Macros

kriti — Thu, 28 Mar 2013 15:38:56 GMT

Hi Can u help me as to where i can find the book: sas macro language 1 essentials

Re: Getting Too Many Record Using Symput Date Macros

Reeza — Thu, 28 Mar 2013 16:37:03 GMT

That looks like a SAS course. I'd recommend enrolling in the course or buying the book off the SAS press if its available.

https://support.sas.com/edu/schedules.html?id=246&ctry=US

The SAS Advanced Certification Guide also has a really good section on macro programming.