Accessing values of different columns using MACRO

Reply
New Contributor
Posts: 3

Accessing values of different columns using MACRO

Hi,

   Can you please help me here. The issue is that the all values in prob_of_survival_in_dec2016 column are the same. 

However, tenure , t6 and t12 are correct so I am not able to understand behavior of SAS over here. It does 

rowwise calculations so below should work. I am trying to access values of different columns through this macro

 

Data is output of proc lifetest and I have manipulated it to appear like

cust_no   tenure(months)  interval0  interval1 interval2........interval6 ..........interval12.....interval20.......interval100

   1                   10                   0.99         0.8         0.89               0.88                .0.7              0.5

    2                   25                    .95          0.97       .96                 .95

    3

 

%let tenure_str = 0;
%let tenure_next6months = 1;
%let tenure_next12months= 2;

%macro calculate_tenures;
data forecast;
set z; 


tenure= round(time_since_sale_months,1);
t6=tenure+6;
t12=tenure+12;

call symput('tenure_str',tenure);
call symput('tenure_next6months',t6);
call symput('tenure_next12months',t12);

prob_of_survival_in_dec2016 = interval&tenure_next6months/interval&tenure_str;

run;

 

%mend calculate_tenures;
%calculate_tenures

 

==========

I also tried trim(interval)&tenure but no difference.

Super User
Posts: 19,157

Re: Accessing values of different columns using MACRO

1. You don't create a macro variable and use it in the same data step. 

2. Usually when someone is trying to do that, the process is too complicated. 

 

Can you explain what your trying to calculate and the rules. 

 

Would an array work? Typically when dealing with data in a wide format arrays are helpful. 

New Contributor
Posts: 3

Re: Accessing values of different columns using MACRO

[ Edited ]

1] I am just trying to calculate conditional probability of survival in next months.

 

So for customer Id 1:  Say, Present tenure is 6 months. So his prob of surviving next 6 months is    (repsective cell value in interval12 column)/ (repsective cell in interval6 column) 

 

2] There are around 900 columns so will array work ?.....I will first like to explore macros

 

 

Thanks Reeza

Super User
Posts: 19,157

Re: Accessing values of different columns using MACRO

The number of columns don't matter and arrays are easier to deal with than macros. Macros are slower and more difficult to program. 

New Contributor
Posts: 3

Re: Accessing values of different columns using MACRO

[ Edited ]

1] As you mentioned, we cannot create a macro variable and use it in the same data step In setp1, I will just create the columns tenure, t6 and t12

>> Now, dataset will be like

 

cust_no   time(months)  interval0  interval1 interval2........interval6 ..........interval12.....interval18.......interval100    tenure    t6    t12

   1                   6                   0.99         0.8         0.89               0.88                .0.7              0.5                                    6         12     18

    2                   25                    .95          0.97       .96                 .95

    3

 

So how can i use columns t6, t12 in a macro and access  the division values 0.7  .88

 

2] using arrays

ok. I already have all the data for all columns in single dataset z.

So if I use arrays I wil have to traverse each row and save their values in 1 array.

Any suggestions on how I can efficiently use arrays in this case ?

 

Thanks

 

Super User
Posts: 19,157

Re: Accessing values of different columns using MACRO

SAS operates per row so that's how it works anyways. 

 

I can't help with current problem because I don't understand what your trying to do. 

You code above does a single iteration which results in the same value. The macro doesn't do very much at all. 

 

The variables you try to reference aren't shown in your sample data. Please post data that more clearly illustrates your issue, and an example of what your output should be. It's preferable is the output actually lines up with the sample data. 

Here's some guidelines on how to prepare a good example http://stackoverflow.com/help/mcve

 

 

Ask a Question
Discussion stats
  • 5 replies
  • 335 views
  • 0 likes
  • 2 in conversation