DATA Step, Macro, Functions and more

How to write the code for this logic

Reply
Occasional Contributor
Posts: 12

How to write the code for this logic

 

 

Hi All,

 

My data looks like this

 

ID      '28-Feb-14'n  ...'28-Feb-16'n  Policy_Start

101     90.20           .....  30.89           21JAN2014

102     76.90          ....... 45.67           13MAR2015

103     66.66           ...... 67.45            20JUN2015

 

i need to add up all the columns which are less than this formula

New_Column=(sum(all_columns_which are less than policy_start+365 )<(policy_start+365))

please kindly note all the columns names are with date.

So how i refer only those columns to sum up .

 

Please revert asap.

 

Thanks,

Vamsi.

 

 

Trusted Advisor
Posts: 1,022

Re: How to write the code for this logic

Question:  what about leap years?  Do you really want policy_start+365, or do you want INTNX('year',policy_start,1,'s')?  (look it up).

 

pseudo-code:

 

  •   Make a DATA step, using
    • array values {*} '28-feb-14'n -- '28-feb-16'n;
  • Loop over the values array:
    • Get the name of the variable (see VNAME function).  This yields a character value like "28-feb-14".
    • Convert that character value to a sas date value (see the INPUT function)
    • if the value is less than the target date, add the corresponing element of values to sum

 

 

    

 

Occasional Contributor
Posts: 12

Re: How to write the code for this logic

can you please write down the sample code
Super User
Posts: 19,822

Re: How to write the code for this logic

Transpose your data to a long format makes this much easier. 

 

Dates shouldn't be variable names - you're storing information in your variable names. 

This is fine for reporting but not great for data analysis. 

It's harder to develop dynamic processes around variable names that constantly change and the number of variables will also change. And when adding data you have to add new columns instead of rows. 

 

Can you change your data to a long format for analysis and then transpose for reporting? 

Ask a Question
Discussion stats
  • 3 replies
  • 197 views
  • 3 likes
  • 3 in conversation