DATA Step, Macro, Functions and more

Macro variable to set dynamic length of variable

Reply
Occasional Contributor
Posts: 10

Macro variable to set dynamic length of variable

Hi,

The below code has its inputs assigned by a proc SQL into statement. So for each line of a statement I cycle through a "control table" assign the variables and run %macro builder. each iteration of the macro builder appends the data to the bottom of one of two permanent tables.  The table is dictated by a concatenation of &horizon + &Level.  In reality &horizon is always equal to TEM and &level is equal to either tab1 or tab2.  So end result is perm.TEMtab1 or perm.TEMtab2.  The macro runs and produces the desired results although I get a warning around &level as it is sometimes length 5 and sometimes len 20.  More specifically when &level is equal to tab1 it is equal to 5 and when tab2 length is 20.

Is there a way I can add to the select statement a condition which sets the length as either 5 or 20 dependant upon the value assigned to &level.  My understanding is there should be however I have nt managed to get it working. Thanks for reading.

 

%macro builder(horizon=,level=,output_name=,output_row=,date_var=,calculation=,data_source=,where=);
proc sql;
create table &horizon._&level as
select unit, compress(&domain) as domain length =2, &level length =20, "&output_name" as output_name length=50, &output_row as output_row,

%do i=1 %to 10
sum(case when datdif(date()-&dateoffset, &date_var, 'act/act') = &i then &calculation
else 0
end) as d&i
%if &i < 10 %then %do;
   ,
  %end;
%end;

from &data_source
%unquote(&where)
group by business_unit, domain, &level;
quit;

proc append base=perm.&horizon._&level data=TEM_&level force;
%mend builder;


Super User
Super User
Posts: 6,502

Re: Macro variable to set dynamic length of variable

If you want to set the length of the variable you are creating based on the NAME of the variable (&LEVEL) then you can do it this way.  Basically create a local macro variable that you will set to 5 or 20 based on the value of LEVEL.  I am not sure what logic you want, below I show how to do it by have a list of values that cause the variable to be defined with length=5 .

%macro builder

(horizon=

,level=

,output_name=

,output_row=

,date_var=

,calculation=

,data_source=

,where=

);


%local i length short;

%let short = AAA BBB CCC ;

%if %sysfunc(indexw(%upcase(&short),%upcase(&level)) %then %let length=5;

%else %let length=20 ;

proc sql;

  create table &horizon._&level as

    select

      unit

    , compress(&domain) as domain length=2

    , &level length=&length

    , "&output_name" as output_name length=50

    , &output_row as output_row

%do i=1 %to 10

    , sum(case when datdif(date()-&dateoffset, &date_var, 'act/act') = &i

                then &calculation

                else 0 end) as d&i

%end;

    from &data_source

    %unquote(&where)

    group by business_unit, domain, &level

  ;

quit;

proc append base=perm.&horizon._&level data=TEM_&level force;

run;


%mend builder;

Ask a Question
Discussion stats
  • 1 reply
  • 160 views
  • 1 like
  • 2 in conversation