topic Re: How to use macro to create new variables in SAS Programming

How to use macro to create new variables

caveman529 — Sun, 13 Oct 2013 23:30:47 GMT

Dear All:

I'm new to macro. Need some help to write my first macro. I hope to deflate 100 variables in the table by a variable. I wrote the following:

May I ask how to code such that the variable names will be automatically create according to a convention. For example, I know different deflator. The variable name is always original_viariable_name / deflator_name. Thank you!

%let vars = var1 var2 var3 ;

data want;

set have;

&vars._deflator = &vars / deflator;

run;

Hope to get var1_deflator, var2_deflator, and var3_deflator, etc....

Thanks !!!

Re: How to use macro to create new variables

esjackso — Mon, 14 Oct 2013 11:58:07 GMT

I think you are confusing macros and arrays / do loops.

To do what you what you can use:

data want;

drop i;

set have;

array n(3) var1 var2 var3;

array result(3) var1_deflator var2_deflator var3_deflator;

do i = 1 to 3;

result(i) = n(i) / deflator ;

end;

run;

Depending on how your variables are named you might be able to streamline the coding of the 100 variables.

Re: How to use macro to create new variables

caveman529 — Wed, 16 Oct 2013 04:03:03 GMT

Thank you so much! It works -

Re: How to use macro to create new variables

caveman529 — Sat, 19 Oct 2013 22:56:29 GMT

The code works, but I have to manually name each of the variables to be created.

May I ask how to code such that the variable names will be automatically create according to a convention. For example, I know different deflator. The variable name is always original_viariable_name / deflator_name. Thank you!

Re: How to use macro to create new variables

Haikuo — Sat, 19 Oct 2013 23:46:49 GMT

Then you need some tweak on Eric's code:

proc sql;

select cats(name,'_deflator') into :newvar separated by ' ' from dictionary.columns

where libname='WORK' AND MEMNAME='HAVE'; /*HAVE TO BE ALL CAPPED*/

select count(name) into :ct trimmed from dictionary.columns

where libname='WORK' AND MEMNAME='HAVE'; /*HAVE TO BE ALL CAPPED*/

QUIT;

data want;

drop i;

set have;

array n(&ct.) var1--var3;/*from the first var to the last var, order matters and don't skip*/

array result(&ct.) &newvar.;

do i = 1 to &ct.;

result(i) = n(i) / deflator ;

end;

run;

Re: How to use macro to create new variables

Tom — Sat, 19 Oct 2013 23:58:22 GMT

You do not need to run the query twice to count the number of values return. PROC SQL already puts that value into the automatic macro variable SQLOBS.

Re: How to use macro to create new variables

Haikuo — Sun, 20 Oct 2013 00:11:11 GMT

Thanks, Tom. Why I always forget about it.:smileysilly:

Haikuo

Re: How to use macro to create new variables

caveman529 — Sun, 20 Oct 2013 07:27:21 GMT

Hi, Hai:

I tried your awesome code. It is reporting the following. I think the reason is on my table, there are a few identifier variable (character variables) that cannot be divided. My previous writing is kind of confusing. All hope to achieve is that for SOME of the numeric variables in the table, I hope to divide them by a common variable called deflator. The new variables' naming convention is OLD_VARIABLE_NAME__deflator. The calculation would be like OLD_VARIABLE__deflator = OLD_VARIABLE / deflator ;

I could easily do this for one or two but when it is approaching hundreds, it is just too....

24 data r.funda2;

25 drop i;

26 set r.funda1;

27 array n(&ct.) gvkey--fundaid;/*from the first var to the last var,

27 ! order matters and don't skip*/

NOTE: The array n has the same name as a SAS-supplied or user-defined

function. Parentheses following this name are treated as array

references and not function references.

SYMBOLGEN: Macro variable CT resolves to 20

ERROR: fundaid does not follow gvkey on the list of previously defined

variables.

ERROR: Too few variables defined for the dimension(s) specified for the array

28 array result(&ct.) &newvar.;

SYMBOLGEN: Macro variable CT resolves to 20

SYMBOLGEN: Macro variable NEWVAR resolves to gvkey_AT datadate_AT fyear_AT

FYR_AT AT_AT CAPX_AT CEQ_AT CSHO_AT DVC_AT LT_AT NI_AT OANCF_AT

SALE_AT XAD_AT XRD_AT SICH_AT prcc_f_AT endfyr_AT begfyr_AT

funda_id_AT

Re: How to use macro to create new variables

Haikuo — Sun, 20 Oct 2013 14:59:35 GMT

Looks like your real world is less friendly than the scenario you presented, which happens all the time .

I would try following:

1) rename the first array 'n' to something else, like '_n'. N() denotes a SAS internal function, so it may confuses SAS, as being reflected in the log.

2) Make sure all of the your array variables are numeric in this case.

3)Define 'SOME', are they adjacent to each other? are there other rules? maybe we can figure out a way to automate it.

Haikuo

Re: How to use macro to create new variables

Tom — Sun, 20 Oct 2013 15:32:57 GMT

Build the list of new and old variable names at the same time and then use two parallel arrays.

proc sql;

select name

, cats(name,'_deflator')

into :oldvars separated by ' '

, :newvars separated by ' '

from dictionary.columns

where libname='WORK'

and MEMNAME='HAVE'

and upcase(name) ne 'DEFLATOR'

and type = 'num'

;

quit;

data want;

set have;

array _old &oldvars ;

array _new &newvars ;

do over _old ;

_new = _old / deflator ;

end;

run;

Re: How to use macro to create new variables

caveman529 — Tue, 22 Oct 2013 07:12:33 GMT

OH YEAH!!!!! IT WORKS! I'm so happy. Thank you so much, guys! You are fabulous!

Re: How to use macro to create new variables

caveman529 — Tue, 22 Oct 2013 07:12:45 GMT

Thank you !!!