BookmarkSubscribeRSS Feed
qwererty
Calcite | Level 5

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;


1 REPLY 1
Tom
Super User Tom
Super User

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;

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
  • 1 reply
  • 1474 views
  • 1 like
  • 2 in conversation