BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Luke93
Calcite | Level 5

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!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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)

 

View solution in original post

10 REPLIES 10
LinusH
Tourmaline | Level 20

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
Kurt_Bremser
Super User

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.

Luke93
Calcite | Level 5

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

Kurt_Bremser
Super User

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.

Luke93
Calcite | Level 5

Thanks Kurt, this works 🙂

 

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

Kurt_Bremser
Super User

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");

?

 

Kurt_Bremser
Super User

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)

 

Luke93
Calcite | Level 5
Apparently worked! Thanks a lot 🙂
RW9
Diamond | Level 26 RW9
Diamond | Level 26

"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.

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
  • 10 replies
  • 5886 views
  • 2 likes
  • 4 in conversation