BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
caveman529
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
esjackso
Quartz | Level 8

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

11 REPLIES 11
esjackso
Quartz | Level 8

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

caveman529
Calcite | Level 5

Thank you so much!  It works -

caveman529
Calcite | Level 5

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!

Haikuo
Onyx | Level 15

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;

Tom
Super User Tom
Super User

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.

Haikuo
Onyx | Level 15

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

Haikuo

caveman529
Calcite | Level 5

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

Haikuo
Onyx | Level 15

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

caveman529
Calcite | Level 5

Thank you !!!

Tom
Super User Tom
Super User

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;

caveman529
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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