topic Proc SQL and multiple variable prompts/counts in SAS Procedures

Proc SQL and multiple variable prompts/counts

xezus — Thu, 21 Jul 2016 19:23:01 GMT

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.

Re: Proc SQL and multiple variable prompts/counts

ballardw — Thu, 21 Jul 2016 19:51:47 GMT

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.

Re: Proc SQL and multiple variable prompts/counts

xezus — Thu, 21 Jul 2016 20:13:20 GMT

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

Re: Proc SQL and multiple variable prompts/counts

ballardw — Thu, 21 Jul 2016 20:19:08 GMT

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.

Re: Proc SQL and multiple variable prompts/counts

xezus — Thu, 21 Jul 2016 20:26:18 GMT

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.

Re: Proc SQL and multiple variable prompts/counts

ballardw — Thu, 21 Jul 2016 20:50:01 GMT

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.

Re: Proc SQL and multiple variable prompts/counts

xezus — Thu, 21 Jul 2016 21:07:41 GMT

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!

Re: Proc SQL and multiple variable prompts/counts

xezus — Fri, 22 Jul 2016 20:02:34 GMT

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.

Re: Proc SQL and multiple variable prompts/counts

ballardw — Fri, 22 Jul 2016 22:00:19 GMT

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 ?