DATA Step, Macro, Functions and more

Unbalanced quotation marks

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

Unbalanced quotation marks

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?


Accepted Solutions
Solution
‎09-17-2011 12:20 AM
Super User
Posts: 9,691

Re: Unbalanced quotation marks

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

View solution in original post


All Replies
Super User
Super User
Posts: 6,502

Re: Unbalanced quotation marks

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)

Super User
Posts: 9,691

Re: Unbalanced quotation marks

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

Contributor
Posts: 22

Unbalanced quotation marks

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

Super User
Super User
Posts: 6,502

Unbalanced quotation marks

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

Solution
‎09-17-2011 12:20 AM
Super User
Posts: 9,691

Re: Unbalanced quotation marks

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

PROC Star
Posts: 7,366

Re: Unbalanced quotation marks

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' 

;

Valued Guide
Posts: 2,175

Unbalanced quotation marks

there is  DEQUOTE()

Contributor
Posts: 22

Unbalanced quotation marks

Thanks Tom, Ksharp and Art297 for your answers.

Trusted Advisor
Posts: 1,300

Re: Unbalanced quotation marks

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

PROC Star
Posts: 7,366

Re: Unbalanced quotation marks

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'

;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 1590 views
  • 6 likes
  • 6 in conversation