07-21-2016 02:45 PM - edited 07-21-2016 03:23 PM
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.
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);
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;
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.
07-21-2016 03:51 PM
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.
07-21-2016 04:02 PM - edited 07-21-2016 04:13 PM
Here is the hard coded, multiple sector run, no errors, everything works fine.
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.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.03 seconds
cpu time 0.04 seconds
07-21-2016 04:19 PM
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.
07-21-2016 04:25 PM - edited 07-21-2016 04:26 PM
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.
07-21-2016 04:50 PM
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.
07-21-2016 05:07 PM
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.
07-22-2016 04:02 PM
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.
07-22-2016 06:00 PM
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 ?