BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Analyticguru_09
Fluorite | Level 6

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

10 REPLIES 10
Tom
Super User Tom
Super User

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
Super User

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
Fluorite | Level 6

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
Super User Tom
Super User

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
Super User

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
Opal | Level 21

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
Rhodochrosite | Level 12

there is  DEQUOTE()

Analyticguru_09
Fluorite | Level 6

Thanks Tom, Ksharp and Art297 for your answers.

FriedEgg
SAS Employee

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
Opal | Level 21

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'

;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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