Currently on SAS enterprise 7.1
I am trying to create a new table with Proc SQL that follows two prompts (one to select company, and one to select sector). Company is a single choice and works fine, whereas sector can have multiple variables that need to be included into the table. It will not recognize anything with a count number after, such as §or_2. I appreciate any help you can provide correcting this syntax.
I am not super comfortable with macro commands, but I have seen people complete this task with macro's before.
I have tried using the "case when then else" statement with syntax below.
proc sql;
create table Total as
select L.*, R.*
from Volumes as L
LEFT JOIN HDDs as R
on L.RPT_ST = R.AREA1
and L.Year = R.Year
and L.month = R.month
where comp_id = "&Comp_ID" and
(case when sector_count=1 then (and L.sector="§or") else ((Case when (§or_1 is not null) then (and L.sector="§or_1")(Case when (§or_2 is not null) then (and L.sector="§or_2")end);
quit;
The full command is below and features my second attempt with different syntax that gives me the same errors.
proc sql; create table Total as select L.*, R.* from Volumes as L LEFT JOIN HDDs as R on L.RPT_ST = R.AREA1 and L.Year = R.Year and L.month = R.month where comp_id = "&Comp_ID" and Case When §or_count = 1 THEN (and L.sector = "§or") when §or_count = 2 Then (and L.sector_1 = "§or_1") when §or_count = 3 Then (and L.sector_2 = "§or_2") When §or_count = 4 THEN (and L.sector_3 = "§or_3") when §or_count = 5 Then (and L.sector_4 = "§or_4") when §or_count = 6 Then (and L.sector_5 = "§or_5") When §or_count = 7 THEN (and L.sector_6 = "§or_6") when §or_count = 8 Then (and L.sector_7 = "§or_7") when §or_count = 9 Then (and L.sector_8 = "§or_8") When §or_count = 10 THEN (and L.sector_9 = "§or_9") when §or_count = 11 Then (and L.sector_10 = "§or_10") when §or_count = 12 Then (and L.sector_11 = "§or_11") When §or_count = 13 THEN (and L.sector_12 = "§or_12") when §or_count = 14 Then (and L.sector_13 = "§or_13") when §or_count = 15 Then (and L.sector_14 = "§or_14") When §or_count = 16 THEN (and L.sector_15 = "§or_15") when §or_count = 17 Then (and L.sector_16 = "§or_16") when §or_count = 18 Then (and L.sector_17 = "§or_17") else L.sector = "§or" end; quit;
Log:
proc sql; 57 create table Total as 58 select L.*, R.* 59 from Volumes as L 60 LEFT JOIN HDDs as R 61 on L.RPT_ST = R.AREA1 62 and L.Year = R.Year 63 and L.month = R.month 64 where comp_id = "&Comp_ID" and 65 Case 66 When §or_count = 1 THEN (and L.sector = "§or") _ 22 76 ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=. ERROR 76-322: Syntax error, statement will be ignored.
Before worrying about the prompt did you have a query that worked correctly for multiple hard coded sector values? (Such as testing the code for Sector= 1 and 2 for instance).
Get that working and show that and then we may have some ideas on how to use the sector prompt.
Here is the hard coded, multiple sector run, no errors, everything works fine.
54
55 proc sql;
56 create table Total as
57 select L.*, R.*
58 from Volumes as L
59 LEFT JOIN HDDs as R
60 on L.RPT_ST = R.AREA1
61 and L.Year = R.Year
62 and L.month = R.month
63 where comp_id = "&Comp_ID" and
64 sector = "DRES" or sector = "DCOM";
WARNING: Variable YEAR already exists on file WORK.TOTAL.
WARNING: Variable MONTH already exists on file WORK.TOTAL.
NOTE: Table WORK.TOTAL created, with 16938 rows and 19 columns.
65 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.03 seconds
cpu time 0.04 seconds
So it looks like from this:
sector = "DRES" or sector = "DCOM"; (which could have been: sector in ("DRES", "DCOM")
that you want to turn that sector prompt into a bunch of OR statements or create an IN comparison.
Yeah, im trying to have a drop down where I can select one to seventeen different sectors from a menu prompt, which on the prompt side seems to be working fine, the code itself just will not cooperate with it.
Thats why I was trying to do states of "if sector_count = 1 then" blah blah, "if sector_count =2 then" blah blah type of thing. I do not know how to make multiple §or variables for the prompts. I thought adding the _1 or _xnumber would create itself as a count.
So §or would be the first selected from the list, §or_1 would be 2nd selected and so forth.
Here's an example of creating a new macro variable from your prompt result
%let sector = DRES DCOM;
data _null_;
length r $ 200;
do i= 1 to countw("§or");
r= catx(',',r,quote(scan("§or",i)));
end;
call symputx("newsector",r);
run;
%put &newsector;
It creates a quoted list of comma separated words (assumes NONE of your sector values have spaces in them) in the NEWSECTOR macro variable that can be used as
and sector in (&newsector);
You wouldn't want to use "&newsector" because of the quotes inside the variable.
Add the data _null_ step earlier in your process.
If you have many longer sector names than the displayed one you might need to make the R varaible longer than 200 characters to hold the longest possible list.
Thank you so much for your help. I will give this code a try when I get back to my computer in a few hours and let you know how it goes.
Thanks again!
This is definitely getting me closer.
I see it now can generate all of the count variables but I am being presented a new error focused on the macro syntax.
GOPTIONS NOACCESSIBLE;
112 %LET _CLIENTTASKLABEL=;
ERROR: Open code statement recursion detected.
113 %LET _CLIENTPROJECTPATH=;
ERROR: Open code statement recursion detected.
114 %LET _CLIENTPROJECTNAME=;
ERROR: Open code statement recursion detected.
115 %LET _SASPROGRAMFILE=;
ERROR: Open code statement recursion detected.
116 %SYMDEL Comp_ID;
ERROR: Open code statement recursion detected.
117 %SYMDEL Sector17;
ERROR: Open code statement recursion detected.
Everything I looked up regarding this error seems to think the macro is running another macro within it. the "end;" in your code I would think finished the first macro run, and then allowed the "%put" to operate independently, but it does not seem to agree with me, haha.
You've introduced a lot of variables that were not mentioned previously. The OPEN CODE usually occurs when using a macro function that requires it to be inside a %macro / %mend definition such as %IF or %DO and related. Getting an error like that with a simple %let statement makes me believe that there is a lot of code you're not showing. By any chance are you attempting to create a new macro definition using macro variables, i.e. generate a %macro / %mend ?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.