Macro to surround a list of values with a single quote

[rramcharan]

Hi I have a variable being passed from excel which is

x=CIRT201808, CIRT201608, 18DI01;

I want to surround each value with a single quote then use a macro to say

if z in ('CIRT201808', 'CIRT201608', '18DI01') then do something.

Any ideas?

Cheers

Rob

[PaigeMiller · Accepted solution]

This works on a string that has the variables separated by spaces and no commas (which you can create using the TRANSLATE function if your original text string has the commas). Alternatively, the macro can be modified to handle the commas, but I didn't do that.

%macro put_quotes_around(string);
	%let aggregate=;
	%do i=1 %to %sysfunc(countw(&string));
		%let thisword=%scan(&string,&i,%str( ));
		%let thiswordq=%nrstr(%')&thisword%nrstr(%');
		%if &i < %sysfunc(countw(&string)) %then %let comma=%str(,); %else %let comma=;
		%let aggregate=&aggregate &thiswordq &comma;
	%end;
	%put &=aggregate;
%mend;
%put_quotes_around(CIRT201808 CIRT201608 18DI01)

[rramcharan]

excellent thanks!

[PaigeMiller]

I should add, for completeness, to use this macro for example, in PROC SQL, comment out the %PUT statement near the end, then add a line that just says &aggregate as the last line of the macro just above the %mend; command, then

proc sql;
      something something something ...
      where variable in (%put_quotes_around(CIRT2018080 CIRT201608 18DI01));
quit;

[RW9]

How do you "get a variable passed from Excel"?  If you have data, then store it in a dataset, then use the dataset, it is far simpler, easier coding, and more robust to do:

proc sql;
  create table want as
  select * 
  from   have
  where z in (select param from paramdataset);
quit;

So paramdataset would look like:
Param

CIRT201808

CIRT201608

...

The above dataset is expandable far beyond the range of macro variables, and is far simpler to work with.

[PaigeMiller]

Suppose he gets a string from Excel. The string is: CIRT201808, CIRT201608, 18DI01 (with or without the commas)

I realize it's not an ideal way to do things, but sometimes you don't get to choose the format of the data because others are creating the Excel file and you have no control over it.

[RW9]

data want (keep=word);
  do i=1 to countw(string_from_excel," ");
    word=scan(string_from_excel,i," ");
    output;
  end;
run;

Whilst not knowing how the string is got, i.e. if its an import just read it into a dataste there, its pretty simple to break it out into data, this may however be overkill for 1 or 2 parameters, but in that case just change the source.

I agree of course that sometimes there are restrictions, but how many times are those restrictions self imposed?

[PaigeMiller]

---

@RW9 wrote:

data want (keep=word);
  do i=1 to countw(string_from_excel," ");
    word=scan(string_from_excel,i," ");
    output;
  end;
run;

Whilst not knowing how the string is got, i.e. if its an import just read it into a dataste there, its pretty simple to break it out into data, this may however be overkill for 1 or 2 parameters, but in that case just change the source.

I agree of course that sometimes there are restrictions, but how many times are those restrictions self imposed?

---

Yes you could type this code every time you encounter this situation to get the string values into a data step; or you could simply, one time, create a macro to do get the end result you want (as I have done) and avoid the data step portion, and then use %put_quotes_around(&macrovariablename) ... which is easier to remember, and in my opinion, more readable.

By the way, yes this is data, it is string data, so data steps will do a fine job on it; and macros work well on string data in this case.

[Ksharp]

if z in ('CIRT201808', 'CIRT201608', '18DI01') then do something.

-->

if find(symget('x'),strip(z)) then do something.

[Ksharp]

if z in ('CIRT201808', 'CIRT201608', '18DI01') then do something.

-->

if find(symget('x'),strip(z)) then do something.