BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
omega1983
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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';

View solution in original post

4 REPLIES 4
Reeza
Super User

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';

kriti
Calcite | Level 5

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

Reeza
Super User

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.

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 654 views
  • 0 likes
  • 3 in conversation