## Insert data to new columns based on analysis result on multiple columns by using do loops

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.

1 ACCEPTED SOLUTION

Accepted Solutions

## Re: Insert data to new columns based on analysis result on multiple columns by using do loops

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;``````
3 REPLIES 3

## Re: Insert data to new columns based on analysis result on multiple columns by using do loops

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

## Re: Insert data to new columns based on analysis result on multiple columns by using do loops

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;``````
Discussion stats
• 3 replies
• 133 views
• 0 likes
• 2 in conversation