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;
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;
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.
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.