How to get rid of this error?

[sas_9]

This is a part of code from sceduled job on taro.

This code is created by another person and i am trying to figure out where exactly i need to put double quatation mark. Becasue code_ref table is in oracle and i think i cannot put double quatation in oracle. Please guide me.

I have atatched code.

Thanks.

[Patrick]

Looks to me as if there is something wrong with the string in &service: .... ,'!'7','!'8'

You've got unbalanced quotes here. You will have to revisit the code where you build the string for &service.

In case there are no & and % in your values then easiest would be to use double quotes: ..... ,"!'7","!'8"

190        and serv in ( &service1)
NOTE: Line generated by the macro variable "SERVICE1".
10                                                         The SAS System                            05:00 Thursday, January 5, 2012

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

[LarryWorley]

Patrick is pointing in the right direction.

Looking at the code you provided just prior to the error message,  I see

proc sql;

  create table dst_codes as

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

quit;

proc sort data = dst_codes;

  by account;

run;

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;

proc sql;

  select serv into :service1 from temp1;

quit;

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

My take is that the code is building a dataset, temp1, which is simply a list of the values of serv from the database table, intl_codes.  It then performs an un-needed sort and create a new dataset, temp1,  which has a single observation and a single variable, serv, which has quoted concatenated values of the original serv separated by commas.  This concatenated value is then fed into a macro variable which is used in the SQL where clause and which is causing your problem.

1.  I would check the values of serv brought back from the data base in temp1.  I suspect some of the values contain a bang character, '!' and a single quote character.  I suspect values of "|'7" and "|'8" in the list.

2.  If the database does contain those characters, then verify if those are valid values.

3.  If they are not valid values, then initiate a clean-up of the database table, intl_codes.

4. If they are valid values, then the best option may be to substitute a sub-query, select distinct serv from dst_codes, for the macro variable, &service1, in the create table SQL statement. Then SAS should build the query so that it will execute correctly. 

Give this a try.  If it works, you can get rid of the proc sort, datastep and proc sql between creating intl_codes and sasds.intl_dst_&day.

[DouglasMartin]

Are you perhaps trying to escape the quote using an exclamation mark? i.e. are you trying to get the two character sequences '7 and '8 as members of that list? If that is the case you can't do it that way in SAS. If for some weird reason that I don't understand you are unable to use double quotes in your code, you can also use two consecutive single quotes as a literal single quote. e.g. x='''7'; assigns the two characters '7 to x. If you actually want the exclamation mark in there, then x='!''7'; assigns the three characters !'7 to x.