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
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
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!
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!
ASKER
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
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;
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
PS:
just figured out - solution 1 won't work:
APR2011 < AUG2010 - since it compares character values. Rather use Solution 2.
Dazz
just figured out - solution 1 won't work:
APR2011 < AUG2010 - since it compares character values. Rather use Solution 2.
Dazz
ASKER
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
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
ASKER
Thanks Dazz :)
ASKER
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!
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!
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;