How to use macro to create new variables

Accepted Solution Solved
Reply
Regular Contributor
Posts: 161
Accepted Solution

How to use macro to create new variables

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 !!!


Accepted Solutions
Solution
‎10-14-2013 07:58 AM
Super Contributor
Posts: 333

Re: How to use macro to create new variables

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.

EJ

View solution in original post


All Replies
Solution
‎10-14-2013 07:58 AM
Super Contributor
Posts: 333

Re: How to use macro to create new variables

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.

EJ

Regular Contributor
Posts: 161

Re: How to use macro to create new variables

Thank you so much!  It works -

Regular Contributor
Posts: 161

Re: How to use macro to create new variables

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!

Respected Advisor
Posts: 3,124

Re: How to use macro to create new variables

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;

Super User
Super User
Posts: 6,502

Re: How to use macro to create new variables

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.

Respected Advisor
Posts: 3,124

Re: How to use macro to create new variables

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

Haikuo

Regular Contributor
Posts: 161

Re: How to use macro to create new variables

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

       n.

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

Respected Advisor
Posts: 3,124

Re: How to use macro to create new variables

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

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

Regular Contributor
Posts: 161

Re: How to use macro to create new variables

Thank you !!!

Super User
Super User
Posts: 6,502

Re: How to use macro to create new variables

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  Smiley Surprisedldvars 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;

Regular Contributor
Posts: 161

Re: How to use macro to create new variables

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 441 views
  • 6 likes
  • 4 in conversation