BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MeganE
Pyrite | Level 9

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
MeganE
Pyrite | Level 9

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!

View solution in original post

12 REPLIES 12
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
MeganE
Pyrite | Level 9

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

MeganE
Pyrite | Level 9

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.

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

MeganE
Pyrite | Level 9

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

FreelanceReinh
Jade | Level 19

Hello @MeganE,

 

As a quick fix you could write 

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

 

MeganE
Pyrite | Level 9

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;


 

 

 

MeganE
Pyrite | Level 9

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!

FreelanceReinh
Jade | Level 19

Glad to read you found a solution.

 

Two remarks:

  1. You didn't copy my code correctly: For the macro processor, MMDDYY and "MMDDYY" are not equal.
  2. 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").
MeganE
Pyrite | Level 9

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.

Astounding
PROC Star

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.

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 12 replies
  • 1717 views
  • 0 likes
  • 4 in conversation