DATA Step, Macro, Functions and more

Array to loop column name

Occasional Contributor
Posts: 12

Array to loop column name

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)));





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)..


Super User
Posts: 9,886

Re: Array to loop column name

[ Edited ]

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)));



Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Trusted Advisor
Posts: 1,831

Re: Array to loop column name

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;


Ask a Question
Discussion stats
  • 2 replies
  • 3 in conversation