topic Re: %if/%then not recognizing wildcards in SAS Data Management

%if/%then not recognizing wildcards

MeganE — Mon, 01 Feb 2016 15:25:36 GMT

Hello,

SAS 9.3, I would like to cycle variables with a date format through this top half and not have to list out every single date format.

I have tried %like, contains, find(), index(), =:, and nothing works.

This code works:

%macro comparefreqs;

%do i=1 %to 1;

%if "&&format&i." = ("MMDDYY10") %then %do;

data &&name&i.._curr;

set curr.&dsn. (keep=&&name&i);

var=year(&&name&i.);

drop &&name&i.;

rename var=&&name&i.;

run;

blah blah blah

This code does not:

%macro comparefreqs;

%do i=1 %to 1;

%if "&&format&i." like ("MMDDYY%") %then %do;

data &&name&i.._curr;

set curr.&dsn. (keep=&&name&i);

var=year(&&name&i.);

drop &&name&i.;

rename var=&&name&i.;

run;

blah blah blah

…

1701 %comparefreqs;

SYMBOLGEN: && resolves to &.

SYMBOLGEN: Macro variable I resolves to 1

SYMBOLGEN: Macro variable FORMAT1 resolves to MMDDYY10

ERROR: Required operator not found in expression: "&&format&i." like ("MMDDYY%")

ERROR: The macro COMPAREFREQS will stop executing.

The only difference between the two is the = was changed to LIKE. And %like doesn't work.

The =: was my favorite.

%macro comparefreqs;

%do i=1 %to 1;

%if "&&format&i." =: MMDDYY10 %then %do;

data &&name&i.._curr;

set curr.&dsn. (keep=&&name&i);

var=year(&&name&i.);

drop &&name&i.;

rename var=&&name&i.;

run;

blah blah blah

From the log (Format resolves to MMDDYY10, but since it's not MMDDYY10, it's false. ?? Huh?):
SYMBOLGEN: Macro variable FORMAT1 resolves to MMDDYY10
MLOGIC(COMPAREFREQS): %IF condition "&&format&i." =: MMDDYY10 is FALSE

I've tried it with quotes, without quotes, tried =: MMDDYY since i want a wildcard. Nothing.

Thanks,
Megan

Re: %if/%then not recognizing wildcards

RW9 — Mon, 01 Feb 2016 15:36:04 GMT

Hi,

There is not equivalent to the like command in macro language, macro is not a programming language as such, it is a text generator. You could use functions. However, I would advise you reconsider what you are trying to do. If you provide a bit of test data and required output, I am 99.9% certain you can do wht you want in Base SAS, far simpler than all those &&%%&&'s. Even something using call execute would be simpler coding:

data _null_;
  set sashelp.vcolumns;
  if <your_conditon> = "MMDDYY10" then call execute('...');
run;

Re: %if/%then not recognizing wildcards

FreelanceReinh — Mon, 01 Feb 2016 15:44:06 GMT

Hello @MeganE,

As a quick fix you could write

%if %substr(&&format&i,1,6)=MMDDYY %then %do;

Re: %if/%then not recognizing wildcards

MeganE — Mon, 01 Feb 2016 15:57:11 GMT

Well, i'm running frequencies on every un-user-formatted variable in the dataset, except for date variables where we only want to run frequencies on the year. So i'm pulling all of the un-user-formatted variables and their formats into macro variables because if they're date variables i want to pull the date off and if they're not i just want to run straight frequencies and then compare to select off where there's a greater than 2% difference.

Unless i want to list out every variable in the dataset by hand, which i don't want to do, i'd rather do it within a macro.

Re: %if/%then not recognizing wildcards

MeganE — Mon, 01 Feb 2016 16:02:48 GMT

Thanks. I just tried it butnow i'm getting different errors that may be talking about later in the code, but i don't know. It works with the equal, so the change to that one line has to be the cause of the errors.:

%macro comparefreqs;
   %do i=1 %to 1;
   %put "&&format&i.";
       %if %substr(&&format&i.,1,6) = "MMDDYY"
           /*or "&&format&i." = "DATE9"*/ %then %do;
           data &&name&i.._curr;
               set curr.&dsn. (keep=&&name&i);
               var=year(&&name&i.);
               drop &&name&i.;
               rename var=&&name&i.;
           run;

307              from junk.qc12_compare_&&name&i.;
ERROR: Macro keyword IF appears as text.
308          quit;
309
310          /*If there are no records in the table, delete it so
311            it isn't cluttering up the work directory.*/
312          %if &num=0 %then %do;
ERROR: Macro keyword END appears as text.
313              proc datasets library=junk nolist;
314                  delete qc12_compare_&&name&i.;
ERROR: Macro keyword END appears as text.
315              quit;
316          %end;
ERROR: Macro keyword MEND appears as text.
317
318      %end;
319 %mend comparefreqs;
320 %comparefreqs;

Re: %if/%then not recognizing wildcards

MeganE — Mon, 01 Feb 2016 16:09:04 GMT

Found it. I swear i tried %index and it did NOT work, but apparently i was missing %upcase, because this does work.

%macro comparefreqs;
   %do i=8 %to 8;
       %if %index(%upcase(&&format&i),MMDDYY) > 0 %then %do;
           data &&name&i.._curr;
               set curr.&dsn. (keep=&&name&i);
               var=year(&&name&i.);
               drop &&name&i.;
               rename var=&&name&i.;
           run;

Thanks everyone!

Re: %if/%then not recognizing wildcards

RW9 — Mon, 01 Feb 2016 16:19:53 GMT

Well, to repeat. There is no need to resort to macro processing for this. You have the metadata in SASHELP.VCOLUMN (or dictionary.columns if you lik SQL). FYou can use that dataset very simply to find the items you want and then call execute the required code:

data _null_;

set sashelp.vcolumn (where=(index(format,"MMDDYY"));

/* The above gives you a dataset containing all variables which have a date format like the one given */

/* As a datastep is a loop, I can now put code here which will be called for each of those variables */

/* Note that with this syntax you could do all kinds of funky things in here, as you would with any datastep

for example not just dates, but times, you could do some manipulations etc. */

call execute('proc means data=xyz; var='||strip(name)||'; output out=want_'||strip(name)||'; run;');

run;

The above will generate one proc means for each variable with a format of MMDDYY. Simple Base SAS coding.

Re: %if/%then not recognizing wildcards

MeganE — Mon, 01 Feb 2016 16:27:27 GMT

Thanks, but i need to run frequencies on ALL variables, not just date ones. I just need to cycle the date ones through a different bit of code than everything else, before it can be brought back to what everything else is being run through b/c i don't want freqs on the actual date, just on the year.

I do get a list of all variables and their associated formats from the dictionary.columns table. That's what i use to build the macro variables in the first place. I just didn't include that part of the code because there's nothing wrong with it.

Re: %if/%then not recognizing wildcards

FreelanceReinh — Mon, 01 Feb 2016 16:28:44 GMT

Glad to read you found a solution.

Two remarks:

You didn't copy my code correctly: For the macro processor, MMDDYY and "MMDDYY" are not equal.
In your original post you wrote that the condition "&&format&i." = ("MMDDYY10") worked. So, I assumed that your macro variables FORMAT1 etc. contain uppercase strings. Hence, applying %UPCASE should be redundant. However, it's good practice to use it anyway ("defensive programming").

Re: %if/%then not recognizing wildcards

RW9 — Mon, 01 Feb 2016 16:37:23 GMT

So use an if statement:

data _null_;

set sashelp.vcolumn;

length code $200;

if index(name,"MMDDYY") then code="something to only do for date variables";

else code="something to do for all other variables";

call execute('proc means data=xyz; '||code||'; run;');

run;

You can do any processing in a datastep.

Re: %if/%then not recognizing wildcards

Astounding — Mon, 01 Feb 2016 16:51:31 GMT

As a strategy, note that you do not need to create a new data set. Depending on the data set size, this might slow down your application.

SAS contains a YEAR format that can be applied to date variables, that will group observations by year. You can use the original data set, the original variable, and just change the format when getting frequencies.

Re: %if/%then not recognizing wildcards

MeganE — Mon, 01 Feb 2016 16:54:00 GMT

I'm good thanks. I got %index to work.

Re: %if/%then not recognizing wildcards

MeganE — Mon, 01 Feb 2016 16:57:24 GMT

Thanks again. I did run it both with quotes and without. When it didn't run without quotes, i ran it with, so that was just the code that was copied in. Thanks again though.

And that %upcase part? I don't even know. I opened my macro table and all system assigned formats are already in uppercase, so why %upcase made that %index work when it wouldn't work without it, and everything was already upper case, i will never know.