topic Insert data to new columns based on analysis result on multiple columns by using do loops in SAS Programming

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

Moonlight_26 — Thu, 21 Sep 2023 04:29:17 GMT

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.

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

mkeintz — Thu, 21 Sep 2023 20:27:13 GMT

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.

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

Moonlight_26 — Fri, 22 Sep 2023 06:37:03 GMT

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;

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

Moonlight_26 — Fri, 22 Sep 2023 06:39:32 GMT

Much appreciate for your reply 🙂