topic How to use years in variable names in macro in SAS Programming

How to use years in variable names in macro

sae123 — Sun, 11 Nov 2018 21:58:12 GMT

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,

Re: How to use years in variable names in macro

SASKiwi — Sun, 11 Nov 2018 22:22:16 GMT

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.

Re: How to use years in variable names in macro

Astounding — Sun, 11 Nov 2018 22:24:41 GMT

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?

Re: How to use years in variable names in macro

sae123 — Sun, 11 Nov 2018 22:48:40 GMT

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!

Re: How to use years in variable names in macro

Astounding — Sun, 11 Nov 2018 23:37:36 GMT

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.

Re: How to use years in variable names in macro

sae123 — Mon, 12 Nov 2018 00:02:28 GMT

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,

Re: How to use years in variable names in macro

Astounding — Mon, 12 Nov 2018 13:27:19 GMT

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