Help using Base SAS procedures

PROC SQL - INSERT INTO via MACRO VARIABLE - ERR 22:322

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

PROC SQL - INSERT INTO via MACRO VARIABLE - ERR 22:322

[ Edited ]

I am running an apparently simple "PROC SQL" on SAS OnDemand for Academics. 

 

proc sql;
create table tablename (variable1 char(200),variable2 char(200),variable3 char(200),variable4 char(4),variable5 char(4));
insert into tablename (variable1,variable2,variable3,variable4,variable5)
&effects;
quit;

 

where &effects is a string made of 300 statements 

 

values("value1","value2","value3","value4","value5") 
values("value6","value7","value8","value9","value10")
...

 

However, SAS always returns the error: ERR 22-322 : Syntax error. Any hint ?

 

If I substitute &effects with the actual text 'values("value1","value2","value3","value4","value5") values("value6","value7","value8","value9","value10")', no error is thrown. However, this is not a feasible solution, since the program should run automatically on several iterations.

 

Is there any solution? Or an alternative to SQL INSERT INTO ?

 

Thank you!

 

 


Accepted Solutions
Solution
‎05-24-2017 09:07 AM
Super User
Posts: 7,766

Re: PROC SQL - INSERT INTO via MACRO VARIABLE - ERR 22:322

[ Edited ]

Yep, it's the %str functions or the masked double quotes:

%let effects=values("adr_citysend","LARENNH","adr_citysend2","NL","SE");
%let variable=pweekday;
%let value=2;
%let count=2;
%let countrysend=NL;
%let countryrecv=SE;
%let effects1=&effects values("&variable","&value","&variable&count","&countrysend","&countryrecv");
%let effects2=&effects%str( values%(%")&variable%str(%",%")&value%str(%",%")&variable.&count%str(%",%")&countrysend%str(%",%")&countryrecv%str(%"%));

proc sql;
create table tablename (variable1 char(200),variable2 char(200),variable3 char(200),variable4 char(4),variable5 char(4));
insert into tablename (variable1,variable2,variable3,variable4,variable5)
&effects1;
quit;

proc sql;
create table tablename (variable1 char(200),variable2 char(200),variable3 char(200),variable4 char(4),variable5 char(4));
insert into tablename (variable1,variable2,variable3,variable4,variable5)
&effects2;
quit;

Log from that:

24         %let effects=values("adr_citysend","LARENNH","adr_citysend2","NL","SE");
25         %let variable=pweekday;
26         %let value=2;
27         %let count=2;
28         %let countrysend=NL;
29         %let countryrecv=SE;
30         %let effects1=&effects values("&variable","&value","&variable&count","&countrysend","&countryrecv");
31         %let effects2=&effects%str(
31       ! values%(%")&variable%str(%",%")&value%str(%",%")&variable.&count%str(%",%")&countrysend%str(%",%")&countryrecv%str(%"%));
32         


33         proc sql;
34         create table tablename (variable1 char(200),variable2 char(200),variable3 char(200),variable4 char(4),variable5 char(4));
NOTE: Table WORK.TABLENAME created, with 0 rows and 5 columns.
35         insert into tablename (variable1,variable2,variable3,variable4,variable5)
36         &effects1;
NOTE: 2 rows were inserted into WORK.TABLENAME.

37         quit;
NOTE: PROZEDUR SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds
      

38         
39         proc sql;
40         create table tablename (variable1 char(200),variable2 char(200),variable3 char(200),variable4 char(4),variable5 char(4));
NOTE: Table WORK.TABLENAME created, with 0 rows and 5 columns.
41         insert into tablename (variable1,variable2,variable3,variable4,variable5)
42         &effects2;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: Line generated by the macro variable "EFFECTS2".
42          values("adr_citysend","LARENNH","adr_citysend2","NL","SE") values("pweekday","2","pweekday2","NL","SE")
2                                                          Das SAS System                              14:25 Wednesday, May 24, 2017

                                                                              _
                                                                              22
                                                                               _
                                                                               76
ERROR 22-322: Syntax error, expecting one of the following: eine Zeichenkette in Hochkommata, eine numerische Konstante, 
              eine Datetime-Konstante, ein fehlender Wert, +, -, MISSING, NULL, USER.  

ERROR 76-322: Syntax error, statement will be ignored.

43         quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROZEDUR SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

(ignore the peculiar part English/part German messages)

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 7,766

Re: PROC SQL - INSERT INTO via MACRO VARIABLE - ERR 22:322

How do you create the macro variable effects (please post the code exactly as used for one value group).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,426

Re: PROC SQL - INSERT INTO via MACRO VARIABLE - ERR 22:322

Why do you end up with so much data in a macro variable?

options symbolgen;

will let you know if your macro variable is consistent, with 300 lines you might hitting the 32K limitation?

A better solution is to load the data from a file, or another data set.

If you are restricted to a macro variable, you could perhaps change the structure of it to able to resolve it in a DATALINES; block in a data step instead.

Data never sleeps
Super User
Posts: 7,766

Re: PROC SQL - INSERT INTO via MACRO VARIABLE - ERR 22:322

Oh, yes, completely overlooked that. In about 100% of cases it is better to keep lists not in macro variables, but in datasets.

As it is with your problem, the complicated "insert into" logic would be replaced by a simple appending step.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: PROC SQL - INSERT INTO via MACRO VARIABLE - ERR 22:322

Posted in reply to KurtBremser

It is created via 

%let effects = &effects%str( values%(%")&variable%str(%",%")&value%str(%",%")&variable.&count%str(%",%")&countrysend%str(%",%")&countryrecv%str(%"%));

%put &effects returns

values("pweekday","2","pweekday2","NL","SE") values("pweekday","6","pweekday3","NL","SE") 
             values("pweekday","5","pweekday4","NL","SE") values("pweekday","4","pweekday5","NL","SE") 
             values("pweekday","3","pweekday6","NL","SE") values("pthour","6","pthour1","NL","SE") 
             values("pthour","7","pthour2","NL","SE") values("pthour","15","pthour3","NL","SE") 
             values("pthour","16","pthour4","NL","SE") values("pthour","13","pthour5","NL","SE") 
             values("pthour","10","pthour6","NL","SE") values("pthour","14","pthour7","NL","SE") 
             values("pthour","9","pthour8","NL","SE") values("pthour","4","pthour9","NL","SE") 
             values("pthour","8","pthour10","NL","SE") values("pthour","11","pthour11","NL","SE") 
             values("pthour","17","pthour12","NL","SE") values("pthour","12","pthour13","NL","SE") 
             values("adr_countrysend","NL","adr_countrysend1","NL","SE") 
             values("adr_citysend","SCHARNEGOU","adr_citysend1","NL","SE") 
             values("adr_citysend","LARENNH","adr_citysend2","NL","SE")

As for the datastep option: how would that be possible?

The values are generated dinamycally after every iteration - that's why I am using a macro.

As far as I know, macro variables cannot be used into DATALINES statement.

 

Finally, I can say the macro variable is read correctly (if I remove the last parenthesis, SAS knows it). 

SYMBOLGEN returns the whole content of the variable

Super User
Posts: 7,766

Re: PROC SQL - INSERT INTO via MACRO VARIABLE - ERR 22:322

You can't use macro variables in datalines, but you can use them to set values:

data new_data;
set basetable (obs=1); /* retrieves attributes */
variable1 = "&variable";
variable2 = "&value";
variable3 = "&variable&count";
variable4 = "&countrysend";
variable5 = "&countryrecv";
run;

proc append data=new_data base=basetable;
run;

Next comes the question if there is any necessity at all to temporarily store data in macro variables. The macro preprocessor is not built for handling data, but for creating dynamic program text, which makes keeping data in macro variables such tedious work.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: PROC SQL - INSERT INTO via MACRO VARIABLE - ERR 22:322

Posted in reply to KurtBremser

Thanks Kurt, this works Smiley Happy

 

I used a similar function before, but due to performance issues and excessive execution time (5000 lines = 5000 data steps ! ) decided to switch to a single variable. 

 

To me, ERR 22:322 still seems nonsense...

Super User
Posts: 7,766

Re: PROC SQL - INSERT INTO via MACRO VARIABLE - ERR 22:322


Luke93 wrote:

 

To me, ERR 22:322 still seems nonsense...


There are things in macro variables and macro calls/functions that are not visible for the programmer, but are used by the macro processor; especially when using the masking functions %str, %quote and so on.

Have you tried to simply append your macro variable by using

%let effects=&effects values("&variable","&value","&variable&count","&countrysend","&countryrecv");

?

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎05-24-2017 09:07 AM
Super User
Posts: 7,766

Re: PROC SQL - INSERT INTO via MACRO VARIABLE - ERR 22:322

[ Edited ]

Yep, it's the %str functions or the masked double quotes:

%let effects=values("adr_citysend","LARENNH","adr_citysend2","NL","SE");
%let variable=pweekday;
%let value=2;
%let count=2;
%let countrysend=NL;
%let countryrecv=SE;
%let effects1=&effects values("&variable","&value","&variable&count","&countrysend","&countryrecv");
%let effects2=&effects%str( values%(%")&variable%str(%",%")&value%str(%",%")&variable.&count%str(%",%")&countrysend%str(%",%")&countryrecv%str(%"%));

proc sql;
create table tablename (variable1 char(200),variable2 char(200),variable3 char(200),variable4 char(4),variable5 char(4));
insert into tablename (variable1,variable2,variable3,variable4,variable5)
&effects1;
quit;

proc sql;
create table tablename (variable1 char(200),variable2 char(200),variable3 char(200),variable4 char(4),variable5 char(4));
insert into tablename (variable1,variable2,variable3,variable4,variable5)
&effects2;
quit;

Log from that:

24         %let effects=values("adr_citysend","LARENNH","adr_citysend2","NL","SE");
25         %let variable=pweekday;
26         %let value=2;
27         %let count=2;
28         %let countrysend=NL;
29         %let countryrecv=SE;
30         %let effects1=&effects values("&variable","&value","&variable&count","&countrysend","&countryrecv");
31         %let effects2=&effects%str(
31       ! values%(%")&variable%str(%",%")&value%str(%",%")&variable.&count%str(%",%")&countrysend%str(%",%")&countryrecv%str(%"%));
32         


33         proc sql;
34         create table tablename (variable1 char(200),variable2 char(200),variable3 char(200),variable4 char(4),variable5 char(4));
NOTE: Table WORK.TABLENAME created, with 0 rows and 5 columns.
35         insert into tablename (variable1,variable2,variable3,variable4,variable5)
36         &effects1;
NOTE: 2 rows were inserted into WORK.TABLENAME.

37         quit;
NOTE: PROZEDUR SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds
      

38         
39         proc sql;
40         create table tablename (variable1 char(200),variable2 char(200),variable3 char(200),variable4 char(4),variable5 char(4));
NOTE: Table WORK.TABLENAME created, with 0 rows and 5 columns.
41         insert into tablename (variable1,variable2,variable3,variable4,variable5)
42         &effects2;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: Line generated by the macro variable "EFFECTS2".
42          values("adr_citysend","LARENNH","adr_citysend2","NL","SE") values("pweekday","2","pweekday2","NL","SE")
2                                                          Das SAS System                              14:25 Wednesday, May 24, 2017

                                                                              _
                                                                              22
                                                                               _
                                                                               76
ERROR 22-322: Syntax error, expecting one of the following: eine Zeichenkette in Hochkommata, eine numerische Konstante, 
              eine Datetime-Konstante, ein fehlender Wert, +, -, MISSING, NULL, USER.  

ERROR 76-322: Syntax error, statement will be ignored.

43         quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROZEDUR SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

(ignore the peculiar part English/part German messages)

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: PROC SQL - INSERT INTO via MACRO VARIABLE - ERR 22:322

Posted in reply to KurtBremser
Apparently worked! Thanks a lot Smiley Happy
Super User
Super User
Posts: 7,942

Re: PROC SQL - INSERT INTO via MACRO VARIABLE - ERR 22:322

"The values are generated dinamycally after every iteration - that's why I am using a macro.

As far as I know, macro variables cannot be used into DATALINES statement."

 

Post example test data (in the form of a datastep) and what you want out, of what your exact problem is.  It is very hard to say from minmal information, but what I can tell you is that this is not really the function of Macro language (i.e. creating data like this).  The simplest answer is to take the code generating the randomm data, and instead of creating macro lists, write out to a plain CSV file, then read that in with a datastep import.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 452 views
  • 2 likes
  • 4 in conversation