Link to home
Start Free TrialLog in
Avatar of SASnewbie
SASnewbie

asked on

SAS Datastep conditional logic

Hello,

Below is the logic I have to populate a variable "Curr_Del" by comparing 3 variables (O21, pmt_due, pmt_trans, of the format type mmyys10.).

What I am need to accomplish is this:
- If O21 = pmt_due and O21 <= pmt_trans then Curr_Del = "Current"
- Else Curr_Del = "Delq"

rsubmit;
data o21_curr_del_test;
set o21_pmt_match2;
by client loan_num;
if o21_actual_date ne .;
if o21 eq pmt_due then
do;
     if pmt_trans le o21 then
          curr_del = 'Current';
end;
else curr_del = 'Delq';
run;
endrsubmit;

I had it working once and now have been looking at it so long I need assistance.

thanks,
SASnewbie
Avatar of theartfuldazzler
theartfuldazzler
Flag of South Africa image

Hi

1.  I normally use WHERE clauses, rather than IF statements to select records - the IF statement can give interesting / confusing results.

2.  I have pretty much just taken your second IF statement, and coded it into SAS.  If this doesn't work, you will need to put the log and maybe a test file to show the problem:

data o21_curr_del_test;
set o21_pmt_match2;
by client loan_num;
where o21_actual_date ne .;
If (O21 = pmt_due) and (O21 <= pmt_trans) then Curr_Del = "Current";
Else Curr_Del = "Delq";

run;
Avatar of SASnewbie
SASnewbie

ASKER

Hi Art,

Thanks for your fast response. I'm going to begin using WHERE clauses bebcause I am certainly getting confusing and unexpected results.

I'll get back to you shortly!
Hi art,

I still return only one row with a current value. I'm attaching an Excel spreadsheet that with a formula returns the expected 36 rows with a current value out of 36K+ rows.


Curr-del.xls
Hi

From the file you sent me, I am guessing that the problem is the following:

The dates you are comparing are (Apr-11) vs (Apr-11) and they are not equal.  In SAS,the format doesn't change the underlying value - and SAS is probably seeing 1Apr2011 vs 21Apr2011 - which are not equal.

There are two solutions:

1.  Compare the formatted values :

data o21_curr_del_test;
set o21_pmt_match2;
by client loan_num;
where o21_actual_date ne .;
If (put(O21,monyy7.) = PUT(pmt_due,monyy7.)) and (PUT(O21,monyy7.) <= PUT(pmt_trans,monyy7.)) then Curr_Del = "Current";
Else Curr_Del = "Delq";
RUN;

or 2) Convert all dates to the end of the month:

data o21_curr_del_test;
set o21_pmt_match2;
by client loan_num;
O21_b = INTNX("Month", O21,0,'e');
pmt_due_b = INTNX("Month", pmt_due,0,'e');
pmt_trans_b = INTNX("Month", pmt_trans,0,'e');

where o21_actual_date ne .;
If (O21_b = pmt_due_b) and (O21_b <= pmt_trans)_b then Curr_Del = "Current";
Else Curr_Del = "Delq";

format pmt_due_b pmt_trans_b o21_b DATE9.;
run;

ASKER CERTIFIED SOLUTION
Avatar of theartfuldazzler
theartfuldazzler
Flag of South Africa image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
PS:

just figured out - solution 1 won't work:

APR2011 < AUG2010 - since it compares character values.  Rather use Solution 2.

Dazz
Hi,

I'm going to give that a try.

I doubt this makes a difference, but the Apr-11 is what Excel converts the format to. In SAS the format is 04/2011.

Thanks again
Thanks Dazz :)
Hi Dazz,

Just a minor order change in the comparison logic and its PERFECT!!!

data o21_curr_del_test;
set o21_pmt_match2;
by client loan_num;
O21_b = INTNX("Month", O21,0,'e');
pmt_due_b = INTNX("Month", pmt_due,0,'e');
pmt_trans_b = INTNX("Month", pmt_trans,0,'e');

where o21_actual_date ne .;
If (O21_b = pmt_due_b) and (pmt_trans_b <=O21_b ) then Curr_Del = "Current";
Else Curr_Del = "Delq";

format pmt_due_b pmt_trans_b o21_b DATE9.;
run;

Thanks for your help again!