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.
... View more
