topic Re: Macro to surround a list of values with a single quote in SAS Programming

Macro to surround a list of values with a single quote

rramcharan — Tue, 02 Oct 2018 13:04:57 GMT

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

Re: Macro to surround a list of values with a single quote

PaigeMiller — Tue, 02 Oct 2018 13:21:24 GMT

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)

Re: Macro to surround a list of values with a single quote

rramcharan — Tue, 02 Oct 2018 13:19:15 GMT

excellent thanks!

Re: Macro to surround a list of values with a single quote

PaigeMiller — Tue, 02 Oct 2018 13:38:57 GMT

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;

Re: Macro to surround a list of values with a single quote

RW9 — Tue, 02 Oct 2018 14:04:26 GMT

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.

Re: Macro to surround a list of values with a single quote

PaigeMiller — Tue, 02 Oct 2018 15:12:03 GMT

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.

Re: Macro to surround a list of values with a single quote

RW9 — Wed, 03 Oct 2018 07:51:33 GMT

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?

Re: Macro to surround a list of values with a single quote

Ksharp — Wed, 03 Oct 2018 12:49:56 GMT

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

-->

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

Re: Macro to surround a list of values with a single quote

Ksharp — Wed, 03 Oct 2018 12:57:59 GMT

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

-->

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

Re: Macro to surround a list of values with a single quote

PaigeMiller — Wed, 03 Oct 2018 13:26:41 GMT

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