MACRO ERROR

[sas_9]

I am running following code and getting error. I am assuming i need to change code value from single quote to double quote but not sure how and where.

Please help.

data temp1;

set dst_codes;

by account;

format serv $300.;

retain serv;

if first.account then

serv = "'"||code||"'" ;

else do;

serv = compress(serv||",'"||code||"'", ' ');

end;

if last.account then

output;

drop code;

run;

NOTE: There were 13 observations read from the data set WORK.DST_CODES.

NOTE: The data set WORK.TEMP1 has 1 observations and 2 variables.

NOTE: Compressing data set WORK.TEMP1 increased size by 100.00 percent.

Compressed is 2 pages; un-compressed would require 1 pages.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

proc sql;

select serv into :service1 from temp1;

quit;

NOTE: The PROCEDURE SQL printed page 2.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

libname idst oracle user=BA01NPTNE password=XXXXXXXXX path=iddp2 schema=ggs readbuff = 250000;

NOTE: Libref IDST was successfully assigned as follows:

Engine: ORACLE

Physical Name: iddp2

  proc sql;

create table sasds.intl_dst_&day as

select compress(put(a.acctcorp,z5.)||'/'||put(a.house,6.)||'/'||put(a.cust,2.), ' ') as acctnum,

serv as bill_code,

put(a.acctcorp,Z5.)as acctcorp

from idst.idst_cust_rate as a,

idst.idst_customer as b

where a.acctcorp = b.acctcorp

and a.house = b.house

and a.cust = b.cust

and b.stat in ('3','4','5')

and serv in ( &service1)

NOTE: Line generated by the macro variable "SERVICE1".

10 The SAS System 05:00 Thursday, April 12, 2012

190 '*V','*X','*Y','>G','B:','E$','E+','.(','.)','.+','_8','!'7','!'8'

                                                              ___

                                                               49

                                                                    _

                                                                   22

                                                                   76

NOTE 49-169: The meaning of an identifier after a quoted string may change in a future SAS release. Inserting white space between

a quoted string and the succeeding identifier is recommended.

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, ), ','.

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

191 and put(a.acctcorp,z5.) ne '05645';

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

[Astounding]

Sandy,

First, the source of the problem is that some of your CODE values contain a quote.  So when you add quotes around the full value, SAS has rules to follow about which quotes are text, and which mark the beginning or end of a string.  And SAS's rules don't match what you want it to do.

Second, switching to double quotes would fix the problem IF none of your CODE values contain a double quote.

In summary, there is a better way.  The QUOTE function adds double quotes, but properly modifies the string it is quoting in case it contains a double quote.  So you would start with:

if first.account then serv = QUOTE(code);

(The caps are for emphasis ... lower case is fine.)

Then:

else serv = trim(serv) || ',' || QUOTE(code);

No doubt someone will show it this way:

else serv = catx(serv, ',', QUOTE(code));

But you'll have to check my syntax on that one.  I'm using an older version of SAS that doesn't support CATX so I'm not as familiar with it.

At any rate, just minor changes needed.

[sas_9]

Thanks for your quick replry, appreciate it.

[Tom]

SQL can pull the values into a macro variable for you.

It does not handle multiple levels of account (but your example code will not either).

proc sql noprint ;

  select distinct quote(code) into :service1 separated by ',' from dst_codes;

[sas_9]

Thanks Tom.

[Peter_C]

Tom's approach is almost the best - unless your CODE variable might hold much less than its full length.

In a $8 string the quote(code) function for the value ABC would be "ABC     " (that is 5 blanks after ABC)

One natural way to achieve the trimming is to use the $quote format, like

select distinct code format= $quote200.  into :service1

Here is SASlog for a quick bit of demo SQL

58   Proc sql  noprint ;

59   *create table demo ( code char(8) ) ;

60   insert into demo values ( 'abc')  values( 'def xy') values(' 123' );

NOTE: 3 rows were inserted into WORK.DEMO.

61   *select * from demo ;

62    select distinct quote(code) into :service1 separated by ',' from demo ;

63   %put have &sqlobs distinct (&service1) ;

have 3 distinct (" 123    ","abc     ","def xy  ")

64    select distinct code format=$quote20.  into :service1 separated by ',' from demo ;

65   %put have &sqlobs distinct (&service1) ;

have 3 distinct (" 123","abc","def xy")

66   quit;

NOTE: PROCEDURE SQL used

I was surprised to see that the leading blank on 123 was preserved, but nice to know

[Ksharp]

Why not add another function to eliminate these annoying blanks?

proc sql noprint ;

  select distinct quote(strip(code)) into :service1 separated by ',' from dst_codes;

Ksharp

[sas_9]

i am using 1st reply answer and getting following error:

data sasds.INTL_csg_&day;

set customer_&day;

run;

NOTE: There were 50402 observations read from the data set WORK.CUSTOMER_13APR12.

NOTE: The data set SASDS.INTL_CSG_13APR12 has 50402 observations and 3 variables.

NOTE: Compressing data set SASDS.INTL_CSG_13APR12 decreased size by 43.39 percent.

Compressed is 381 pages; un-compressed would require 673 pages.

NOTE: DATA statement used (Total process time):

real time 0.12 seconds

cpu time 0.11 seconds

proc sql;

145 create table dst_codes as

146 select '123' as account,code from sasds.intl_codes where plan = 'INTL' and biller = 'DST';

NOTE: Compression was disabled for data set WORK.DST_CODES because compression overhead would increase the size of the data set.

NOTE: Table WORK.DST_CODES created, with 13 rows and 2 columns.

147 quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

148

149

150 proc sort data = dst_codes;

151 by account;

152 run;

NOTE: There were 13 observations read from the data set WORK.DST_CODES.

NOTE: The data set WORK.DST_CODES has 13 observations and 2 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

　

9 The SAS System 05:00 Friday, April 13, 2012

153

154

155 data temp1;

156 set dst_codes;

157 by account;

158 format serv $300.;

159 retain serv;

160 if first.account then serv = QUOTE(code);

161 else serv = trim(serv) || ',' || QUOTE(code);

162 if last.account then

163 output;

164 drop code;

165 run;

NOTE: There were 13 observations read from the data set WORK.DST_CODES.

NOTE: The data set WORK.TEMP1 has 1 observations and 2 variables.

NOTE: Compressing data set WORK.TEMP1 increased size by 100.00 percent.

Compressed is 2 pages; un-compressed would require 1 pages.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

166

167 *********This code commented out becasue its getting error*********;

168

169 /*

170 data temp1;

171 set dst_codes;

172 by account;

173 format serv $300.;

174 retain serv;

175 if first.account then

176 serv = "'"||code||"'" ;

177 else do;

178 serv = compress(serv||",'"||code||"'", ' ');

179 end;

180 if last.account then

181 output;

182 drop code;

183 run;

184

185 proc sql;

186 select serv into :service1 from temp1;

187 quit;

188

189 */

190

191

192

193

194 libname idst oracle user=BA01NPTNE password=XXXXXXXXX path=iddp2 schema=ggs readbuff = 250000;

NOTE: Libref IDST was successfully assigned as follows:

Engine: ORACLE

Physical Name: iddp2

195

196 proc sql;

197 create table sasds.intl_dst_&day as

10 The SAS System 05:00 Friday, April 13, 2012

198 select compress(put(a.acctcorp,z5.)||'/'||put(a.house,6.)||'/'||put(a.cust,2.), ' ') as acctnum,

199 serv as bill_code,

200 put(a.acctcorp,Z5.)as acctcorp

201 from idst.idst_cust_rate as a,

202 idst.idst_customer as b

203 where a.acctcorp = b.acctcorp

204 and a.house = b.house

205 and a.cust = b.cust

206 and b.stat in ('3','4','5')

207 and serv in ( &service1)

_

22

WARNING: Apparent symbolic reference SERVICE1 not resolved.

207 and serv in ( &service1)

________

202

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant,

a missing value, (, -, SELECT.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

208 and put(a.acctcorp,z5.) ne '05645';

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

209 quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.02 seconds

cpu time 0.01 seconds

[sas_9]

is that because of i commented out following code part too?

185 proc sql;

186 select serv into :service1 from temp1;

187 quit;

[Astounding]

Yes, that's the part that creates &SERVICE1.

Alternatively, you could change the DATA step that you already have:

if last.account then call symputx('service1', serv);

[Ravinder]

The point where you are resolving the macro variable, place the qouting functions such as Quote or BRqoute there and try to proceed with the code.

For instance it should be quote(&service1). Please let me know if the problem still persists.