Unbalanced quotation marks

[Analyticguru_09]

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?

[Tom]

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)

[Ksharp]

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

[Analyticguru_09]

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

[Tom]

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

[Ksharp · Accepted solution]

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

[art297]

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' 

;

[Peter_C]

there is  DEQUOTE()

[Analyticguru_09]

Thanks Tom, Ksharp and Art297 for your answers.

[FriedEgg]

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

[art297]

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'

;