BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Criptic
Lapis Lazuli | Level 10

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;

  %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;

  %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"

           _____________

           49

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.

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

View solution in original post

6 REPLIES 6
dcruik
Lapis Lazuli | Level 10

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;

Tom
Super User Tom
Super User

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'


ballardw
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Criptic
Lapis Lazuli | Level 10

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;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

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
  • 6 replies
  • 3320 views
  • 3 likes
  • 5 in conversation