Build a macro variable of multiple values based on day of week

[Jeff_DOC]

Good morning.

I'm attempting build a macro variable based on the day of the week the program runs. I've tried so many variations but nothing I do seems to work correctly. I have a feeling (and it's just a feeling) that it has to do with my quotes or commas but even with removing them I can't seem to get this to work. I'm sure there's a better way to accomplish this and I'm definitely open to suggestions. LOCA1 should run each time the program runs regardless of the day of the week. The other LOCA? should only be included on the respective days of the week. The string created will later be used in a PROC SQL in statement.

Thank you very, very much for any help someone could provide.

data setup;
attrib
	run_date         length=8       format = mmddyy10.;
	run_date = today();
	day_of_week = weekday(run_date);

	call symput("run_date",run_date);
	call symput('day_of_week',day_of_week);
run;

%macro set_conditions;

%let location = 'LOCA1';

%if &week_day. = 2 %then location = ('LOCA1', 'LOCA2', 'LOCA3');
%if &week_day. = 5 %then location = ('LOCA1', 'LOCA4', 'LOCA5', 'LOCA6');

		call symput("location", location);
%mend;
%set_conditions;

Log:

26 %macro set_conditions;
27
28 %let location = 'LOCA1';
29
30 %if &week_day. = 2 %then location = ('LOCA1', 'LOCA2', 'LOCA3');
31 %if &week_day. = 5 %then location = ('LOCA1', 'LOCA4', 'LOCA5', 'LOCA6');
32
33 call symput("location", location);
34 %mend;
35 %set_conditions;
SYMBOLGEN: Macro variable WEEK_DAY resolves to 2
NOTE: Line generated by the invoked macro "SET_CONDITIONS".
35 location = ('LOCA1', 'LOCA2', 'LOCA3')
________
180
SYMBOLGEN: Macro variable WEEK_DAY resolves to 2

ERROR 180-322: Statement is not valid or it is used out of proper order.

36
37 %_eg_hidenotesandsource;
SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES
SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE
51
52
53 %_eg_hidenotesandsource;
SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES
SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE
56

[Tom]

You cannot run the CALL SYMPUT() method outside of a data step!!

Why would you need it anyway inside of a macro?  Just use a %LET statement to build your new macro variable.

Please note that unless it is critical that the generated macro variable has leading and/or trailing spaces you should almost never be using the ancient CALL SYMPUT() method. Instead use the modern CALL SYMPUTX() method.  That will automatically convert numeric values into strings and also has a additional third argument for controlling which symbol table gets the new macro variable.

[Jeff_DOC]

So I tried the following (if that's what you're intending) and got the error below from the PROC SQL in statement. I tried it both ways to be sure. Thanks for the SUMPUTX suggestion. I'll make that change to modernize this old code.

data setup;
attrib
	run_date         length=8       format = mmddyy10.;
	run_date = today();
	day_of_week = weekday(run_date);

	call symput("run_date",run_date);
	call symput('day_of_week',day_of_week);
run;

%macro set_conditions;

%let location = 'LOCA1';

%if &week_day. = 2 %then %let location = ('LOCA1', 'LOCA2', 'LOCA3');
%if &week_day. = 5 %then %let location = ('LOCA1', 'LOCA4', 'LOCA5', 'LOCA6');

%mend;
%set_conditions;

Attempt 1:

52 and location_code in (location);
________
22
202
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant,
a datetime constant, a missing value, (, -, SELECT.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

Attempt 2:

52 and location_code in (&location.);
_
22
76
WARNING: Apparent symbolic reference LOCATION not resolved.
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant,
a datetime constant, a missing value, (, -, SELECT.

ERROR 76-322: Syntax error, statement will be ignored.

[PaigeMiller]

Please show us the whole log for this block of code, pasted into the window that appears when you click on the </> icon.

There is no apparent code that you have showed us matching this log entry: 52 and location_code in (location); 

so please show us the relevant code as well.

[ballardw]

Can you clearly state the CONDITION value(s) and the desired result for each. Not code, the values.

Showing code that doesn't do what you want does not actually state either the condition or the resulting value.

It might be a very good idea to show how you intend to use this as well. Macro variables containing parentheses,  quotes and commas can be problematic at use.

When you use

%let location = ('LOCA1', 'LOCA2', 'LOCA3');

then the parentheses are part of the value.

You do realize that this error message

52 and location_code in (location);

doesn't reference the macro variable &location.

And if you had referenced the macro variable it would resolve to

and location_code in (('LOCA1', 'LOCA2', 'LOCA3'));

Which may work for some things but doubling parentheses in general is likely not a good habit.

IN requires parentheses, even for a single value. So if  your want to use

Location in 'LOCA1' it must be written as Location in ('LOCA1');

You aren't showing use where the Proc SQL runs in relation to the %set_conditions macro in relation to any other code using the results of BOTH of those. Partial error message also have problems because you might have something in lines way before the error causing issues. Entire Proc or Data step code using the macros is important.

Your %set_conditions macro does not set anything for 5 of the 7 possible week days so seems incomplete. As well as not showing where the value of &week_day get set. It is not macro parameter. It can't be a data set variable. And you show creating &day_of_week, possibly that is supposed to be the macro variable in %set_conditions?

[Jeff_DOC]

Certainly. The example was simply to show the result of the change. The values are multiple instances of each of the values of location from the example: LOCA1 - LOCA6.

proc sql;
create table want_01 as
	select count_date,
		case when location_code = 'LOCAX' then 'LOCA1'
			else location_code
		end as location_code,
	from have
	where location_code in (&location.);

[ballardw]

Where is the call to %set_conditons?

Where is &week_day set?

Those are requirements before this SQL has a chance of running. All the things required to create &location need to be shown.

[Tom · Accepted solution]

Let's try to piece together what you might have run from the various posts you have shown.

First you have this macro definition:

%macro set_conditions;
%let location = 'LOCA1';
%if &week_day. = 2 %then %let location = ('LOCA1', 'LOCA2', 'LOCA3');
%if &week_day. = 5 %then %let location = ('LOCA1', 'LOCA4', 'LOCA5', 'LOCA6');
%mend;

We'll come back to this.

Then you have this data step:

data setup;
  attrib run_date length=8 format=mmddyy10.;
  run_date = today();
  day_of_week = weekday(run_date);
  call symputX("run_date",run_date);
  call symputX('day_of_week',day_of_week);
run;

I corrected the code to use CALL SYMPUTX so RUN_DATE and DAY_OF_WEEK will have a values like 23537 and 2 instead of including lots of unneeded leading spaces.

Then you call the macro:

%set_conditions;

Which should cause an error since you never gave the macro variable WEEK_DAY any value.  

And finally you run some SQL code

proc sql;
create table want_01 as
  select count_date
       , case when location_code = 'LOCAX' then 'LOCA1'
             else location_code
         end as location_code,
  from have
  where location_code in (&location.)
;
quit;

That is referencing a macro variable LOCATION that you did not create since the %LET's in the macro SET_LOCATION never executed.   But even it they had since you never told SAS to make LOCATION as a GLOBAL macro variable it disappeared when the macro SET_CONDITION finished running.

If you want to write a macro that takes as input a week day number and then sets a macro variable it would probably be best to write the macro as having two input parameters.  The value of the week day number and the name of the macro variable to create. Also the value should look the same whether it has one, two or more values.  So something like:

%macro set_conditions(week_day,mvar=location);
%if not %symexist(&mvar) %then %global &mvar;
%let &mvar = 'LOCA1';
%if &week_day. = 2 %then %let &mvar = 'LOCA1'  'LOCA2'  'LOCA3';
%if &week_day. = 5 %then %let &mvar = 'LOCA1' 'LOCA4' 'LOCA5' 'LOCA6';
%mend set_condition;

Which you could call passing in the value of the macro variable you did create:

data setup;
  attrib run_date length=8 format=mmddyy10.;
  run_date = today();
  day_of_week = weekday(run_date);
  call symputX("run_date",run_date);
  call symputX('day_of_week',day_of_week);
run;

%set_conditions(&day_of_week)

And now LOCATION will exist and have a valid value to use in the expression:

where location_code in (&location.)

[Jeff_DOC]

Thank you very much. I'll have to take some time and do some research to figure out exactly what your code does and how it works. But it works perfectly and that's what matters.

[PaigeMiller]

Please @Jeff_DOC show us the entire code you are using with this macro. Don't make us guess at what you are doing.

[Jeff_DOC]

Thanks to everyone who took the time to help out. I apologize for not being the best at explaining the issue and I really appreciate people reading between the lines and helping me.