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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.