topic Re: Array to loop column name in SAS Programming

Array to loop column name

SBRVamsi — Fri, 02 Dec 2016 07:02:55 GMT

Hi team,

Please kindly help me on this logic.

this is my sample code.

data test_data;
set Final_Data_IPAS_PR_Claims;
'28FEB2014'n= min(base_pre,max(0,((19782-'Policy Start Date'n+1)/(Policy_Term*365)*base_pre)));

'31MAR2014'n= min(base_pre,max(0,((19813-'Policy Start Date'n+1)/(Policy_Term*365)*base_pre)));
'30APR2014'n= min(base_pre,max(0,((19843-'Policy Start Date'n+1)/(Policy_Term*365)*base_pre)));
'31MAY2014'n= min(base_pre,max(0,((19874-'Policy Start Date'n+1)/(Policy_Term*365)*base_pre)));
'30JUN2014'n= min(base_pre,max(0,((19904-'Policy Start Date'n+1)/(Policy_Term*365)*base_pre)));
.

run;

I am doing this manally, with many steps i need some array and loop combination to get it right.

I need some logic to create the month end date as column names from 30APR2014 to SEP2016 and the numeric value of the particular coulumn should be used in the condition.

Logic is same across all the columns created. Just i need to change the numeric value highlighted in the formula according to the column date. (Means the numeric value of the month end date should be used there)..

Re: Array to loop column name

Kurt_Bremser — Fri, 02 Dec 2016 08:45:12 GMT

Don't put data in structure.

Instead assign the date to a variable month_end, the value to a properly named variable, and do a output for every single calculation.

This also resolves the issue of having to use the execrable 'some garbage goes in here'n construct for variable names.

data want;
set have;
format month_end date9.;
do month = 4 to 9;
  month_end = intnx('month',mdy(month,1,2016),0,'end');
  value = min(base_pre,max(0,((month_end-'Policy Start Date'n+1)/(Policy_Term*365)*base_pre)));
  output;
end;
run;

Re: Array to loop column name

Shmuel — Fri, 02 Dec 2016 08:51:57 GMT

I developed a code to do the work - see ttached file.

The code generates a program in a directory as defined in first line - FILENAME statement.

Adapt the filename to your instalation.

To run the generated program add at end the line:

%include pgm;

For test I looped from FEB 2014 to FEB 2015;

Addapt required dates on lines: 4, 5, 13.

It can be done dynamicaly if you put the code into a macro:

%macro create(y_start, m_start, y_end, m_end);

/* copy the code here and change lines: */

/* line 4 */ y1 = &y_start;

/* line 5 */ m1 = &m_start;

/* line 13 */ do until(y1 = &y_end and m1 = &m_end);

%mend create;

%create (2014, 02, 2016, 09);

%include pgm;