topic Re: Resolve macro variable within SQL like in SAS Programming

Resolve macro variable within SQL like

Sinistrum — Mon, 04 Mar 2019 18:51:46 GMT

Dear community,

I would please like to ask for your help concerning the following issue.

Using proc sql in a loop, I want to filter out some variables based on their name.

For example, I were to use

proc	sql		noprint;
	select
		name
	into
		:namelist
		separated	by	' '
	from
		contents1
	where
		name	like	'x%level&l.';
quit;

from a data set, whose variable names I obtained via proc contents and safed them in "contents1".

These variables are, say, 100 different x, defined over L, say, 50, different level,

i.e., in the first step, %LET l=1, I want to filter out {x1level1, x2level1, ..., x100level1},

in the second step, %LET l=2, I want to filter out {x1level2, x2level2, ..., x100level2},

...,

in the 50th step, %LET l=50, I want to filter out {x1level50, x2level50, ..., x100level50}

(i.e., I want to loop over the L levels, keeping all 100 x in each loop step).

My problem is that due to the single quotes in the like statements, the macro variable, l, will not get resolved into 1, 2, ..., 50.

Could you please provide me assistance on how to resolve this issue respectively achieve my goal?

Yours sincerely,

Sinistrum

Re: Resolve macro variable within SQL like

34reqrwe — Mon, 04 Mar 2019 18:57:40 GMT

Use double quotes instead so that the macro variables resolve

Re: Resolve macro variable within SQL like

andreas_lds — Mon, 04 Mar 2019 19:05:21 GMT

@34reqrwe wrote:

Use double quotes instead so that the macro variables resolve

That was my first idea, too, but @Sinistrum can't use double quotes, because that would sas look for a macro named level ...

@Sinistrum : why do you need the variable names stored in a macro variable?

Re: Resolve macro variable within SQL like

PGStats — Mon, 04 Mar 2019 19:40:01 GMT

One way out is

name like "x%"||"level&l."

Re: Resolve macro variable within SQL like

Tom — Mon, 04 Mar 2019 19:57:10 GMT

When dealing with messing strings it is usually easier to do it in code instead of macro code.

As an example let's print the values of SASHELP.CLASS that start with H and end with y.

where name like 'H%y';

Only the letter y is in a macro variable name SUFFIX. So let's use a simple data step to generate a new macro variable that contains the value we want in single quotes.

%let suffix=y;
data _null_;
  call symputx('condition',quote(cats('H%',symget('suffix')),"'"));
run;

proc print data=sashelp.class ;
  where name like &condition;
run;

Re: Resolve macro variable within SQL like

Tom — Mon, 04 Mar 2019 20:22:02 GMT

Here is another trick i LIKE to use. Build your string using * as the wildcard and then just replace it with the % at the end.

Then your string that looks LIKE a LIKE value can be build using double quotes to allow macro variable references.

%let like_like="x*level&l.";

Then you can use TRANSLATE() to convert it to a string that uses single quotes and replaces the * with %.

%let like=%sysfunc(translate(&like_like,'%',"*"));

So in your program:

name like %sysfunc(translate("x*level&l.",'%',"*"))

Re: Resolve macro variable within SQL like

Sinistrum — Tue, 05 Mar 2019 06:48:08 GMT

Thank you very much indeed. All three solution work smoothly and once again, your answers illustrated additional concepts I have not been familiar with. Sadly, I can "technically" only "accept" one. However, this case indeed is resolved.

@34reqrweand @andreas_lds: Yes, this sadly was the issue that double quotes would induce an interpretation of % not in the way I would like it to be interpreted (no longer the wildcard in the regular expression search, but the beginning of a macro name).

I store the variable names which I read from a data set in a macro such that I can, in a consecutive step, keep only those variable via a plain keep-command. Although I am afraid this is not the only (let allone most efficient) way to do so and hence does not constitute a necessity, it solves the task I have got at hand - isoltating only a subset of variables from a large date set based on their names, which, in this specific case, happen to follow a certain pattern implied by a certain structure.

The trick to disentangle the whole string into two parts with concatenating them into one string via || is very parsimonious indeed.

I fuhrter highly appreciate the two approaches involving the creation of new macro variables, which made me familiar with the translate-function and its workings. It seems clever and smart to let SAS resolve &l. to a number and pass it on to like after having replaced * by % such that it can handle it as a wildcard.