Macro variable to set dynamic length of variable

qwererty — Fri, 26 Apr 2013 15:17:37 GMT

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;

Re: Macro variable to set dynamic length of variable

Tom — Fri, 26 Apr 2013 16:37:14 GMT

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;

topic Re: Macro variable to set dynamic length of variable in SAS Programming

Macro variable to set dynamic length of variable

Re: Macro variable to set dynamic length of variable