topic Unbalanced quotation marks in SAS Programming

Unbalanced quotation marks

Analyticguru_09 — Fri, 16 Sep 2011 00:53:41 GMT

Hi,

I'm trying to do something like below.

For ex i have a list of stores in a sas dataset:

Datasetname: list

Variable name: stores

The data is as shown below.(sorrounded by quotes)

stores

------

'Walmart'

'Costco'

'bruegger's'

I'm creating the macrovariables in the shownformat below:

data _null_;

set list end=final;

call symput('stores'||left(_n_),trim(stores);

if final then call symput('count',_n_);

run;

Now i'm joining with another table to create some datasets.

%macro loop;

%do a=1 %to &count;

proc sql;

create table test&A as

select a.*,

&&stores&a as stores

from sample as a

where a.store = &&stores&a

;

quit;

run;

%mend;

I was able to successfully create datasets for the first 2 records(walmart,costco)

but receivng an error for 'bruegger's': (error:unbalanced quotation marks). HOW CAN

i acheive that?

Re: Unbalanced quotation marks

Tom — Fri, 16 Sep 2011 01:47:34 GMT

First it is normally not necessary to take values out of a dataset and into macro variables.

Second look into using the QUOTE function.

Taking the second issue first. You can use the QUOTE function to protect the value of your character variables from confusing SAS. This also has the advantage of generating a string that will be valid to compare to a character variable in your WHERE clause later. (A.STORE = "Walmart" instead of A.STORE = Walmart which would cause SAS to look for variable named WALMART.)

(Nitpick alert. I changed your macro variables from STORESxxx to STORExxx as each value is for only one store)

data _null_;

if final then call symputx('count',_n_);

set list end=final;

call symputx('store'||left(_n_),quote(trim(stores));

run;

Back to eliminating putting the values of dataset variables into macro variables.

One way might be to use the FIRSTOBS and OBS dataset options to pull just one record from list at a time and merging it with source data.

Again I have no idea why you are adding a variable called STORES to your output dataset when you already have a variable named STORE that by definition has the same value.

%macro loop(ds1,ds2,prefix) ;

%local i n ;

proc sql noprint ;

select count(*) into :n from &ds1 ;

%do i=1 %to &n ;

create table &prefix.&i as

select a.*

from &ds2 a , &ds1 (firstobs=&i obs=&i) b

where a.store = b.store

;

%end;

quit;

%mend loop;

%loop(ds1=list,ds2=sample,prefix=test)

Re: Unbalanced quotation marks

Ksharp — Fri, 16 Sep 2011 12:39:58 GMT

You can remove the first and last single quote, then use double quote around &&stores&a.

data _null_;

set list end=final;

stores=substr(stores,2,length(stores)-2);

call symput('stores'||left(_n_),trim(stores);

if final then call symput('count',_n_);

run;

..................

"&&stores&a" as stores

from sample as a

where a.store = "&&stores&a "

Ksharp

Unbalanced quotation marks

Analyticguru_09 — Fri, 16 Sep 2011 15:13:03 GMT

Hi ,

My apologizes. I think i didnt put the question cleary.... Please see below:

data _null_;

set list end=final;

call symput('store'||left(_n_),trim(store));

call symput('code'||left(_n_),trim(code));

call symput('REPORTname'||LEFT(_N_),TRIM(REPORTname);

if final then call symput('count',_n_);

run;

%macro loop;

%do a=1 %to &count;

proc sql;

create table Results&A as

select f.var1,

f.var2,

&&store&a as clientname

from saslib.referral as f

where f.codeid = &&code&a

and f.&&reportname&a in (&&store&a)

;

quit;

run;

%end;

%MEND LOOP;

%LOOP;

Dataset stores

stores code reportname

------ ------ -----------

'Walmart' ,'target' 123 GC1 (I can have multiple stores in the column:stores seperated by , and sorrounded by single quotes)

'Costco' 345 GC2

'bruegger's' 456 GC3

(The COLUMN(REPORTNAME) VALUES in the dataset "stores" are the COLUMN NAMES IN THE SASLIB.REFERRAL

DATASET)

The issue is witht he value 'bruegger's'. As it has one more quote inside it, i'm getting an error message

Unbalanced quotation marks

Tom — Fri, 16 Sep 2011 16:53:44 GMT

You should fix the data in the LIST dataset by doubling up the embedded quotes.

So instead of 'bruegger's' you have it stored as 'bruegger''s'.

Re: Unbalanced quotation marks

Ksharp — Sat, 17 Sep 2011 04:20:11 GMT

Your data doese not look good.

I am afraid that you need to clean it firstly.

That is to say change single quote around string into double quote.

Such as:

data temp;
input stores  $40.;
cards; 
'Walmart' ,'target','Costco' 
'Costco'         
'bruegger's' , 'Costco'  
;
run;
data want;
 set temp;
 length _stores $ 40.;
 i=1;  temp=scan(stores,i,',');
 do while (not missing(temp));
 temp=strip(temp);
 _stores=catx(',',_stores,cats('"',substr(temp,2,length(temp)-2),'"'));
 i+1;temp=scan(stores,i,',');
 end;
call symput('store'||left(_n_),trim(_stores));
%put _user_;
run;

GLOBAL STORE1 "Walmart","target","Costco"

GLOBAL STORE2 "Costco"

GLOBAL STORE3 "bruegger's","Costco"

Ksharp

消息编辑者为：xia keshan

Re: Unbalanced quotation marks

art297 — Sun, 18 Sep 2011 13:27:01 GMT

I was hoping that someone would post the prx solution, as I'd like to see it. Shouldn't be hard to write a regular expression that identified quotes that border a string and delete them.

Conversely, a datastep solution isn't very complex anyhow. E.g.,:

data want;

infile cards dsd dlm=",";

input stores :$40. @@;

if index(stores,"'") eq 1 then

stores=substr(stores,2);

if index(strip(reverse(stores)),"'") eq 1 then

stores=substr(stores,1,length(stores)-1);

cards;

'Walmart','target','Costco'

'Costco', 'Sam's'

'bruegger's' , 'Costco'

;

Unbalanced quotation marks

Analyticguru_09 — Sun, 18 Sep 2011 18:59:19 GMT

Thanks Tom, Ksharp and Art297 for your answers.

Unbalanced quotation marks

Peter_C — Mon, 19 Sep 2011 11:49:04 GMT

there is DEQUOTE()

Re: Unbalanced quotation marks

FriedEgg — Wed, 21 Sep 2011 00:07:41 GMT

Art asked to see solution using prxchange...

data _null_;

input foo $10.;

bar=prxchange('s/.(.*)./$1/',-1,strip(foo));

put foo= bar=;

cards;

'Bob's'

'Costco'

'Walmart'

'Uncle's'

'Mikes'

'Jack's'

;

run;

UPDATE: The regular expression will delete the first and last char from given string regardless of what it is, not necessarily a quote in this instance... You would just need to change to "s/'(.*)'/$1/" for explicitly removing leading or trailing single quote

Re: Unbalanced quotation marks

art297 — Wed, 21 Sep 2011 00:41:49 GMT

Yes, that is exactly what I was hoping to see. i.e.:

data _null_;

input foo $10.;

bar=prxchange("s/'(.*)'/$1/",-1,strip(foo));

put foo= bar=;

cards;

'Bob's'

'Costco'

'Walmart'

'Uncle's'

'Mikes'

'Jack's'

;