topic Re: Filtering for text the includes single quote in SAS Programming

Filtering for text the includes single quote

sutzig — Wed, 03 Feb 2021 21:56:48 GMT

Good evening:

I'm stuck creating a proc sql statement filtering for a value that includes a single quote (').

PROC sql;
	create table target as
	select
		*
	from 
		source
	where
		value = "Tom's Trucks"
	quit;
run;

Thanks for your help.

Re: Filtering for text the includes single quote

Reeza — Wed, 03 Feb 2021 22:01:23 GMT

Missing semicolon to end query (in red below)
SQL needs a QUIT, not a RUN (orange/purple below). Remove the RUN and leave the QUIT in and that should be good.

PROC sql;
	create table target as
	select
		*
	from 
		source
	where
		value = "Tom's Trucks" ;
	quit;
run;

@sutzig wrote:

Good evening:

I'm stuck creating a proc sql statement filtering for a value that includes a single quote (').
PROC sql;
	create table target as
	select
		*
	from 
		source
	where
		value = "Tom's Trucks"
	quit;
run;
Thanks for your help.

Re: Filtering for text the includes single quote

sutzig — Fri, 05 Feb 2021 14:11:44 GMT

Reeza:

Thank you for pointing out the syntax error in the sample, but this is not the issue. The issue is creating a filter using a text value criteria which includes a single quote (and, presumably double quotes).

For example

PROC sql;
	create table target as
	select
		*
	from 
		source
	where
		value in=("Tom's Trucks" ; "Carstop");
	quit;
run;

will only return records for Carstop.

Steve

Re: Filtering for text the includes single quote

Tom — Fri, 05 Feb 2021 14:29:13 GMT

Please provide an example of data where you are having trouble filtering. (Note in your example you have mistakenly added an equal sign and typed a semi-colon instead of comma between the quoted list of values.)

proc sql;
create table target as
  select *
  from source
  where value in ("Tom's Trucks" , "Carstop")
;
quit;

Also since this is SQL code are you perhaps not running that WHERE clause in SAS but have actually pushed it into some remote database that has a different understanding of the meaning of double quote and single quotes?