BookmarkSubscribeRSS Feed
xezus
Calcite | Level 5

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 &sector_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="&sector") else ((Case when (&sector_1 is not null) then (and L.sector="&sector_1")(Case when (&sector_2 is not null) then (and L.sector="&sector_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 &sector_count = 1 THEN (and L.sector = "&sector")
when &sector_count = 2 Then (and L.sector_1 = "&sector_1")
when &sector_count = 3 Then (and L.sector_2 = "&sector_2")
When &sector_count = 4 THEN (and L.sector_3 = "&sector_3")
when &sector_count = 5 Then (and L.sector_4 = "&sector_4")
when &sector_count = 6 Then (and L.sector_5 = "&sector_5")
When &sector_count = 7 THEN (and L.sector_6 = "&sector_6")
when &sector_count = 8 Then (and L.sector_7 = "&sector_7")
when &sector_count = 9 Then (and L.sector_8 = "&sector_8")
When &sector_count = 10 THEN (and L.sector_9 = "&sector_9")
when &sector_count = 11 Then (and L.sector_10 = "&sector_10")
when &sector_count = 12 Then (and L.sector_11 = "&sector_11")
When &sector_count = 13 THEN (and L.sector_12 = "&sector_12")
when &sector_count = 14 Then (and L.sector_13 = "&sector_13")
when &sector_count = 15 Then (and L.sector_14 = "&sector_14")
When &sector_count = 16 THEN (and L.sector_15 = "&sector_15")
when &sector_count = 17 Then (and L.sector_16 = "&sector_16")
when &sector_count = 18 Then (and L.sector_17 = "&sector_17")
else L.sector = "&sector"
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 &sector_count = 1 THEN (and L.sector = "&sector")
_
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.

 

8 REPLIES 8
ballardw
Super User

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.

 

 

xezus
Calcite | Level 5

 

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

 

ballardw
Super User

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.

xezus
Calcite | Level 5

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 &sector variables for the prompts.  I thought adding the _1 or _xnumber would create itself as a count.

 

So &sector would be the first selected from the list, &sector_1 would be 2nd selected and so forth.

ballardw
Super User

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("&sector");
      r= catx(',',r,quote(scan("&sector",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.

xezus
Calcite | Level 5

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!

xezus
Calcite | Level 5

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. 

ballardw
Super User

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 ?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 2593 views
  • 0 likes
  • 2 in conversation