Hi All,
I have this problem and looking for help.
I have a dataset like below :
CIF | DPD_1 | DPD_2 | DPD_... | DPD_13 |
1234 | . | . | ... | 40 |
2345 | 201 | 201 | ... | 201 |
3456 | 4 | . | ... | 72 |
4567 | 25 | 45 | ... | 190 |
Now, I need to check whether the CIF ever got the DPD's, we categorize them into Ever30 ( 0-30) , Ever60 (30-60), Ever90 (60-90), Ever120 (90-120), Ever180 ( 120-180), Ever180+ (180++). So, I wrote below queries:
data want;
set have;
length EverXDays 7;
array _DPD{13} DPD_1-DPD_13;
do i = 1 to dim(_DPD);
if _DPD[i] > 0 then do;
EverXDays = 1;
end;
if _DPD[i] > 0 and _DPD[i] <= 30 then do;
Ever30 = 1;
end;
if _DPD[i] > 30 and _DPD[i] <= 60 then do;
Ever60 = 1;
end;
if _DPD[i] > 60 and _DPD[i] <= 90 then do;
Ever90 = 1;
end;
if _DPD[i] > 90 and _DPD[i] <= 120 then do;
Ever120 = 1;
end;
if _DPD[i] > 120 and _DPD[i] <= 180 then do;
Ever180 = 1;
end;
if _DPD[i] > 180 then do;
Ever180Plus = 1;
end;
put _DPD(i);
leave;
end;
run;
But the result wasn't like what I expected.
The expectation is :
CIF | DPD_1 | DPD_2 | DPD_... | DPD_13 | EverXdays | Ever30 | Ever60 | Ever90 | Ever120 | Ever180 | Ever180+ |
1234 | . | . | ... | 40 | 1 | 0 | 1 | 0 | 0 | 0 | 0 |
2345 | 201 | 201 | ... | 201 | 1 | 0 | 0 | 0 | 0 | 0 | 1 |
3456 | 4 | . | ... | 72 | 1 | 1 | 0 | 1 | 0 | 0 | 0 |
4567 | 25 | 45 | ... | 190 | 1 | 1 | 1 | 0 | 0 | 0 | 1 |
Much appreciate for your help. Thanks.
I have the solutions, it works for my case.
Here is the code, so this might help anyone facing the same problem with me 🙂
data want;
set have ;
format Consistency_DPD Ever30 Ever60 Ever90 Ever120 Ever180 Ever180Plus EverXDays Best.;
array _DPD{13} DPD_1-DPD_13;
Consistency_DPD=ifc(range(of DPD:)=0,0,1);
do i = 1 to dim(_DPD);
if _DPD[i] > 0 then do;
EverXDays = 1;
end;
if _DPD[i] > 0 and _DPD[i] <= 30 then do;
Ever30 = 1;
end;
if _DPD[i] > 30 and _DPD[i] <= 60 then do;
Ever60 = 1;
end;
if _DPD[i] > 60 and _DPD[i] <= 90 then do;
Ever90 = 1;
end;
if _DPD[i] > 90 and _DPD[i] <= 120 then do;
Ever120 = 1;
end;
if _DPD[i] > 120 and _DPD[i] <= 180 then do;
Ever180 = 1;
end;
if _DPD[i] > 180 then do;
Ever180Plus = 1;
end;
put _DPD(i);
end;
run;
In the absence of actual sample data in the form of a working data step, here is untested code:
data want (drop=d e);
set have;
everxdays=(min(of dpd_:)>0);
array ev {1:5} ever30 ever60 ever90 ever120 ever180 ;
array map {1:180} _temporary_ (30*1 ,30*2 ,30*3 ,30*4 ,60*5 ) ;
do e=1 to dim(ev); ev{d}=0; end;
array dp {*} dpd_: ;
do d=1 to dim(dp);
if 1<=dp{d}<=180 then ev{map{dp{d}}}=1;
end;
ever_over_180 = (max(of dp{*})>180);
run;
This program assumes that the DP vars all assume integer values.
The temporary array MAP maps all integer values from 1 to 180 to a value of 1 through 5, indexing the five variables ever30, ever60, ever90, ever120, and ever180, which are the five elements of array EV.
For example dp values 1 through 30 are mapped to 1, which means that array element EV{1} (variable ever30) is set to 1. If any dp value is between 31 and 60, then element EV{2} (var ever60) is set to 1, etc.
I have the solutions, it works for my case.
Here is the code, so this might help anyone facing the same problem with me 🙂
data want;
set have ;
format Consistency_DPD Ever30 Ever60 Ever90 Ever120 Ever180 Ever180Plus EverXDays Best.;
array _DPD{13} DPD_1-DPD_13;
Consistency_DPD=ifc(range(of DPD:)=0,0,1);
do i = 1 to dim(_DPD);
if _DPD[i] > 0 then do;
EverXDays = 1;
end;
if _DPD[i] > 0 and _DPD[i] <= 30 then do;
Ever30 = 1;
end;
if _DPD[i] > 30 and _DPD[i] <= 60 then do;
Ever60 = 1;
end;
if _DPD[i] > 60 and _DPD[i] <= 90 then do;
Ever90 = 1;
end;
if _DPD[i] > 90 and _DPD[i] <= 120 then do;
Ever120 = 1;
end;
if _DPD[i] > 120 and _DPD[i] <= 180 then do;
Ever180 = 1;
end;
if _DPD[i] > 180 then do;
Ever180Plus = 1;
end;
put _DPD(i);
end;
run;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.