topic Re: Adding to the value of a macro variable in SAS Programming

Adding to the value of a macro variable

Criptic — Tue, 14 Jul 2015 13:40:13 GMT

I hope this question hasn't been posted before, I couldn't find anything on it, but I don't really know what I am looking for.

I want to append a number of characters to the value of a macro variable depending on the value of other variable(code below) which will then be used in a Select-Statement as my Where condition.

%IF "&SERA" NE "" %THEN

%DO;

%LET WHERE=WHERE SERA="&SERA";

%END;

%IF "&KUMULEREIGNIS" NE "" %THEN

%DO;

%IF "&WHERE" NE "" %THEN

%DO;

%LET WHERE = &WHERE AND KUMULEREIGNIS="&KUMULEREIGNIS";

%END;

%IF "&WHERE" = "" %THEN

%DO;

%LET WHERE = WHERE KUMULEREIGNIS="&KUMULEREIGNIS";

%END;

%IF "&MERKMAL" NE "" %THEN

%DO;

%IF "&WHERE" NE "" %THEN

%DO;

%LET WHERE = &WHERE AND MERKMAL="&MERKMAL";

%END;

%IF "&WHERE" = "" %THEN

%DO;

%LET WHERE = WHERE MERKMAL="&MERKMAL";

%END;

%IF "&SERA" = "" AND "&KUMULEREIGNIS" = "" AND "&MERKMAL" = "" %THEN

%DO;

%LET &WHERE = ;

%END;

These are the messages from the log, the last one is just a %PUT which i am using to see the value of the variable

NOTE: Line generated by the macro variable "WHERE".

637 "WHERE SERA="43"

_____________

NOTE: Line generated by the macro variable "WHERE".

637 "WHERE SERA="43" AND KUMULEREIGNIS="2014_06_09_ELA"

_____________ ______________________

49 49

WHERE SERA="43" AND KUMULEREIGNIS="2014_06_09_ELA"

I hope somebody can help my with this.

Re: Adding to the value of a macro variable

dcruik — Tue, 14 Jul 2015 14:04:21 GMT

I would first start by removing the "" in your macro calls (i.e. %IF "&WHERE"=> %IF &WHERE) as all macro variables are treated as characters already, so the quotes are not needed when calling it in your if statements. Then, when creating your WHERE statement macros in the %LET, try using the %STRING() function to return everything with the quotes and equal sign (i.e %LET WHERE = WHERE MERKEMAL="&MERKMAL"=>%LET WHERE=%STRING(WHERE MERKEMAL="&MERKMAL")).

Hope this helps!

%LET KUMULEREIGNIS=2014_06_09_ELA;

%LET SERA=43;

%LET WHERE=%STR(WHERE SERA="&SERA");

%LET WHERE=%STR(&WHERE AND KUMULEREIGNIS="&KUMULREIGNIS");

%PUT &WHERE;

Re: Adding to the value of a macro variable

ballardw — Tue, 14 Jul 2015 14:08:12 GMT

One: I think you may be overdoing use of "&var", Normal use of the "&var" is to get the value of a macro variable, which is text, used properly inside a data step. Normal use for comparison of macro variables is without quotes.

You don't post the actual error but it looks likely to be unmatched quotes.

If you want to test if the value of a macro variable is blank a better test is

if %sysevalf(%superq(sera)=,boolean) %then %do;

Note the macro variable does NOT have the & preceding to work and the whole expression returns true or false so not compared value is needed.

Run your code with options mprint symbolgen to see what is actually happening and possibly get a better idea where the actual error occurs.

Re: Adding to the value of a macro variable

RW9 — Tue, 14 Jul 2015 14:23:37 GMT

Perhaps post some data and required output. I can't for the life of me see why all that macro code is necessary. If you absolutely need a where statement then do it in a datastep and call symput:

data _null_;

if xyz then call symput('whr_clause','where abc="bcd"');

run;

Re: Adding to the value of a macro variable

Criptic — Tue, 14 Jul 2015 15:05:57 GMT

Based on your idea I came up with another solution and it worked for me. Thanks very much:

DATA _NULL_;
	LENGTH WHERE_FILTER $500;
	sera_filter="&SERA";
	kumul_filter="&KUMULEREIGNIS";
	merk_filter="&MERKMAL";
	IF sera_filter NE "" THEN DO;
		WHERE_FILTER= 'WHERE SERA="'!!sera_filter!!'"';
		IF kumul_filter NE "" THEN DO;
			WHERE_FILTER= TRIM(WHERE_FILTER) !! ' AND KUMULEREIGNIS="'!!kumul_filter!!'"';
			IF merk_filter NE "" THEN DO;
				WHERE_FILTER = TRIM(WHERE_FILTER) !! ' AND MERKMAL="'!!merk_filter!!'"';
			END;
		END;
	END;
	ELSE IF "&KUMULEREIGNIS" NE "" THEN DO;
		WHERE_FILTER='WHERE KUMULEREIGNIS="'!!kumul_filter!!'"';
		IF merk_filter NE "" THEN DO;
				WHERE_FILTER = TRIM(WHERE_FILTER) !! ' AND MERKMAL="'!!merk_filter!!'"';
			END;
	END;
	ELSE IF merk_filter NE "" THEN DO;
		WHERE_FILTER = 'WHERE MERKMAL="'!!merk_filter!!'"';
	END;
	ELSE DO;
		WHERE_FILTER =' ';
	END;
	CALL SYMPUT('WHERE_FILTER', WHERE_FILTER);
RUN;

Re: Adding to the value of a macro variable

RW9 — Tue, 14 Jul 2015 15:20:37 GMT

Well, in some ways helps. First, good idea to fix on a Good Programming Structure, consistent indentation/capitilisation etc. So its easier to read. You can condense it somewhat:

data _null_;

if "&SERA." ne "" then call symput('WHERE_FILTER','sera="&SERA."');

if "&KUMULREGIONS." ne "" then call symput('WHERE_FILTER','kumulregions="&KUMULREGIONS."');

...

run;

Secondly, why do you have 4 variables, to create 4 where clauses? If I had to do this, and I make some assumptions here, I would create a where variable:

data have;

set have;

length where $200;

where_variable=cats(sera,kumulregions,...,...);

run;

Then in your where clause later on with: where cats(&SERA.,&KUMULREGIONS,...,...) = where_variable.

Saves all that messing around. Also note, why have four separate macro variables for each one, again condense that down to one variable, i.e. if only one will be present then cats() the four variables.

Re: Adding to the value of a macro variable

Tom — Tue, 14 Jul 2015 15:48:01 GMT

You can generalize to any list of macro variables that have the property of the macro variable name matching the corresponding data set variable name.

%let sera= ;

%let kumulereignis=A,AA ;

%let merkmal=BBB ;

data _null_;

length where $30000 ;

array vars $500 sera kumulereignis merkmal ;

do i=1 to dim(vars);

vars(i)=symget(vname(vars(i)));

if vars(i) ne ' ' then

where =catx(' AND ',where,catx('=',vname(vars(i)),catq('1as',vars(i))))

;

end;

if where ne ' ' then where='where ' || where ;

put where=;

call symputx('where',where);

run;

where=where kumulereignis='A,AA' AND merkmal='BBB'