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

[Moonlight_26]

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.

[mkeintz]

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.  

[Moonlight_26]

Much appreciate for your reply 🙂

[Moonlight_26 · Accepted solution]

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;