How to refer a variable in a where statement and in the name of the table using %let

[PauloC]

Dear experts, I want to use some %let variables in the following program but it is not working.

The where statement seems to be ok but the name of the table that I want to create in not.

And If I create a variable like X=&Initial_Year; I get a column with the result spread.

Thank You for your help.

%let initial_date='01Feb2021';
%let final_date='28Feb2021';

%let Initial_Year=year(&data_inicial.d);
%let final_Year=year(&data_final.d);
%let initial_month=month(&data_inicial.d);
%let final_month=month(&data_final.d);

data vendas_&Initial_Year._&&initial_month._&&final_Year._&& final_month.;
set dataw.f_Sales;

where ((data>=&dinitial_date.d and data<&inal_date.d) AND (TIPOLIN = 'V') AND (substr(id,1,1) in ('2','3','4','5','6','7','8','9')));

run;

[ballardw · Accepted solution]

When your program throws as many errors as I think it does you really need to include the LOG.

Please double check you code and read the LOG.

I get this from running your %let statements:

20   %let initial_date='01Feb2021';
21   %let final_date='28Feb2021';
22
23   %let Initial_Year=year(&data_inicial.d);
WARNING: Apparent symbolic reference DATA_INICIAL not resolved.
24   %let final_Year=year(&data_final.d);
WARNING: Apparent symbolic reference DATA_FINAL not resolved.
25   %let initial_month=month(&data_inicial.d);
WARNING: Apparent symbolic reference DATA_INICIAL not resolved.
26   %let final_month=month(&data_final.d);
WARNING: Apparent symbolic reference DATA_FINAL not resolved.

So if that was you code the first problem is spelling variable names correctly. You show repeated use of &DATA_INICIAL but the variable you created with %let is INITIAL_DATE, and you use &data_final but created FINAL_DATE.

Second, if you want to use data step functions like YEAR, MONTH or anything else the code has to be wrapped in a %sysfunc(   ) to tell SAS that you want the macro processor to use the function and not just use the TEXT.

Between your first and second problems your log should have show a bunch of undefined macro variable warnings.

Third, it is a good idea to check your macro variable contents, especially when trying something new, by using %PUT to check on the results. Try this and see if that helps with the table names.

%let initial_date='01Feb2021';
%let final_date='28Feb2021';

%let Initial_Year =%sysfunc(year(&initial_date.d));
%let final_Year   =%sysfunc(year(&final_date.d));
%let initial_month=%sysfunc(month(&initial_date.d));
%let final_month  =%sysfunc(month(&final_date.d));

%put Initial_Yea: &Initial_Year Final_year: &final_year ;
%put Initial_month: &initial_month  Final_month: &final_month;

Experience often leads SAS programs to consider placing quotes as part of macro variables often sub-optimal coding.

Turn text in to dates, if needed by placing unquoted values in "&macrovar"d instead, and may even want actual date values instead of the text.

The text version could be used in other statements if you don't have the quotes like a title statement:

Title "Report for data from &initial_date to &final_date";

If those macro variables have quotes though then the syntax may either not run or just look funny.

[PauloC]

Thanks for the help and sorry for my mistakes in the name of the variables.

[sbxkoenk]

@ballardw has already given a sufficient and satisfactory answer I guess, but here's an alternative (to working with %sysfunc()).

I think this is what you're after:

data _NULL_;
 initial_date='01Feb2021'd;         put initial_date date9.; 
 final_date='28Feb2021'd;           put final_date date9.; 
 initial_year=year(initial_date);   put initial_year; 
 final_year  =year(final_date);     put final_year; 
 initial_month=month(initial_date); put initial_month; 
 final_month  =month(final_date);   put final_month;
 call symput('initial_date' ,"'"!!put(initial_date,date9.)!!"'d");
 call symput('final_date'   ,"'"!!put(final_date,date9.)!!"'d"); 
 call symput('initial_year' ,put(initial_year,4.)); 
 call symput('final_year'   ,put(final_year,4.)); 
 call symput('initial_month',strip(put(initial_month,2.))); 
 call symput('final_month'  ,strip(put(initial_month,2.))); 
run;

%PUT *****&=initial_date*****;
%PUT *****&=final_date*****;
%PUT *****&=initial_year*****;
%PUT *****&=final_year*****;
%PUT *****&=initial_month*****;
%PUT *****&=final_month*****;

data work.vendas_&initial_year._&initial_month._&final_year._&final_month.;
 set dataw.f_Sales;
where (     (data>=&initial_date. and data<&final_date.) 
        AND (TIPOLIN = 'V') 
        AND (substr(id,1,1) in ('2','3','4','5','6','7','8','9') ) );
run;
/* end of program */

Cheers,

Koen

[PauloC]

Thanks for the alternative, so I can learn more ...