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;
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';
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';
Hi Can u help me as to where i can find the book: sas macro language 1 essentials
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.
Thanks
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.