How to use years in variable names in macro

[sae123]

Hi,

I have this following binary dataset:

a_0405  a_0506 a_0607 a_0708... a_n etc

Using A_0506 as the base year, how can i instruct SAS to look at future years (i.e. a_0607 a_0708...a_n) and return a 0 if the value of future years is 1?

Please let me know if you require more information and i apologise in advance if the question is unclear.

Many thanks,

[SASKiwi]

Instead of different months data in different variables it would be much easier to process your data if you transformed your data into rows with a MONTH variable that defines the months and just one variable A to hold the "A" data.

[Astounding]

Some of this is definitely unclear:

• When should the program return a 0?  When ALL future years contain 1, or when ANY future years contain 1?
• What should the program return when future years contain something else other than 1?
• What is the last year in your data?

And just to confirm ... it looks like you are asking for the program to add just 1 new variable to your data set.  Is that right?

Are there any other variable names that begin with "a_0" other than these year variables?

[sae123]

Hi Astounding,

In response to your questions - 

• When should the program return a 0?  When ALL future years contain 1, or when ANY future years contain 1?

Return a 0 if ANY future years contain 1

• What should the program return when future years contain something else other than 1?

All future years for the variable contain either a 0 or 1. If all future years contain 0, then return 1 otherwise return 0

• What is the last year in your data?

1617 though i was hoping the macro is capable to identify the latest year (if at all possible)

And just to confirm ... it looks like you are asking for the program to add just 1 new variable to your data set.  Is that right?

That is correct - if all future years for A_N = 0 then new_variable =1

Are there any other variable names that begin with "a_0" other than these year variables?

There are 1.2m observation/clients with and over 300 variables. However, none of these variables share the same "a_0" name. 

The variables represent services and return 1 if the client had used the service for the given year.

Hope this helps to clarify the question!

[Astounding]

Good.  Believe it or not, that's all necessary information.   I hope I understood these responses correctly:

"Return a 0 if ANY future years contain 1"

"If all future years contain 0, then return 1 otherwise return 0"

Here's one way to go about it.

data want;

set have;

array yrs {*} a_0: ;

a_n=1;

do k=1 to dim(yrs) until (a_n=0);

  years = substr(vname(yrs{k}), 3);

  if years > '0506' and yrs{k}=1 then a_n=0;

end;

drop k years;

run;

It's untested code, but looks about right.  Let me know if you encounter any difficulty with it.

[sae123]

Greatly appreciate your help Astounding.

Sorry for the stupid question, but just so that i'm clear -

A_N is the variable, where N is the year (0405, 0506...n etc.)

I'm just not entirely sure on what the A_0 is. Can you please clarify? Is this the new variable?

Many thanks,

[Astounding]

A_N is the new variable that flags whether there are 1's or not in the subsequent years.  Notice the colon:

a_0:

That refers to all variable names that begin with "a_0", making them all part of the array.  Technically, that's a mistake and should be expanded to also include variable names that begin with "a_1" so the full list of array elements should be:

array years {*} a_0: a_1: ;

There is a separate variable ("years") that captures the year designation (0405, 0506, etc.)