How can I create dataset variables from inside a macro function?

[jjknknl]

I currently have a table work.have.  In it are many datetime columns, all of which have names of the form "have_*_date". For each of these datetime variables in the dataset, I would ultimately like to create 2 new variables in the dataset, one for the date and one for the year.  For example, if i have a dataset called have, with 2 datetime variables, have_abc_date and have_def_date, then this is what I do:

data want; set have;
    abc_date = datepart(have_abc_date);
    abc_year = year(abc_date);

    def_date = datepart(have_def_date);
    def_year = year(def_date);

    format abc_date def_date date9.;
run;

This works, without any errors.  However, I would now like to make this into a macro so I can easily perform this for any given dataset.  The datetime variable names will always start with the name of the table and end with "_date", as above.  What I have so far is this:

%macro newvars(lib,table,newtable);
    proc sql noprint;
        select name
        into :datenames
        separated by " "
        from dictionary.columns
        where libname="&lib" and
        memname="&table" and
        substr(name,length(strip(name))-4,5)="_date"
        ;
    quit;

    data WORK.&newtable; set &db..&table;
        %local i dn;
        %let i=1;
        %do %while (%scan(&datenames, &i) ne );
            %let dn = %scan(&datenames, &i);

            newname1 = %sysfunc(datepart(&dn));
            newname2 = %sysfunc(year(newname1));
            format newname1 date9.;
            rename newname1 = %sysfunc(tranwrd(&dn,&table._,%str()))
                         newname2 = %sysfunc(tranwrd(newname1,_date,_fyyr));

            %let i = %eval(&i + 1);
        %end;
    run;
%mend;
%newvars(lib=WORK, table=have, newtable=want);

The first part, the part that creates the datenames macro variable, works.  But I'm getting errors after that.  It is saying 

ERROR: Argument 1 to function DATEPART referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.
ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list. Execution of %SYSCALL statement or %SYSFUNC
or %QSYSFUNC function reference is terminated.

Can someone please advise me on how to fix my macro function?  If trying to create temporary variables newname1 and newname2 and then rename them every iteration is the wrong approach to doing what I'm trying to do, please advise me on a better way.  Thanks!

[ballardw · Accepted solution]

You really should provide a small data set of what you start with and what the desired result is. You have several common errors in mixing macro functions, data step functions and the relationship of macro functions to dataset variables.

One thing you must learn if you are going to code macros are the options to debug them. Primary are the options Mprint and symbolgen that will provide much more information that the base log sees for macros without them.

I would suggest running this code:

first turn on the options:

options mprint symbolgen;

%newvars(lib=WORK, table=have, newtable=want);

then turn of the options:

options nomprint symbolgen;

Mprint will display the text created when a statement like

 newname1 = %sysfunc(datepart(&dn));

is resolved by the macro processor.

Also the errors will appear closer to the text that created them instead of all at the end of the macro execution.

Some details:

When you use proc sql to look at dictionary.columns the dataset variable Memname will contain all uppercase names.

So when you passed table=have as a parameter then it didn't find any matching records. So the sql should be modified from:

 memname="&table"  to memname=upcase("&table").

Variablenames can be mixed case, so likely need to use:

lowcase( substr(name,length(strip(name))-4,5) )="_date"

incase you have a variable like have_Last_Date.

If your pattern is always looking for the last bit to be date consider using:

   lowcase( scan(name,1,'_','B') ) = 'date'

   scan(name,1,'_','B') finds word looking backward at the string separated by _

You can also use the SAS automatic macro variable &sqlobs to tell you how many results were returned from the name search.

This can be helpful because you could test the result and skip the data step if none are returned. Best is to save sqlobs into a known macro variable as it disappears very quickly:

%let mydatecounter = &sqlobs; immediately after the Proc Sql step.

Inside the data step you show these lines are incorrect:

newname1 = %sysfunc(datepart(&dn)); %sysfunc returns a value to the macro processor, not the data set and

you would want

newname1 = datepart(&dn);

which has a problem if there are more than one date variables because ALL of them are going to be assigned to newname1.

Using the mydatecounter actually simplifies the loop to build multiple statements.

%do i=1 %to &mydatecounter;
   %let dn = %scan(&datenames,&i);
     %let newname1 =  %sysfunc(tranwrd(&dn,&table._,%str()));
    &newname1 = datepart(&dn);
    format &newname1 date9.;
   %let newname2= %scan(&newname1,1,_)_year;
   &newname2 = year(&newname1);
%end;

which also builds the variables directly instead of rename which can be a little picky at times. Your second rename wasn't doing close to what you thought it was:

%let dummy = %sysfunc(tranwrd(newname1,_date,_fyyr));
%put &dummy;

the tranwrd was searching for the literal text "_date" in the literal text "newname1" so no replacement was made.

Use _fyyr instead of _date but I built the loop using the example before the macro.

I am not at all sure what this is attempting to do:

 %sysfunc(tranwrd(&dn,&table._,%str()))

it implies that every one of your date variables has the string, for your example, have_ in the date variable name. Which is a VERY strong requirement for a "generic" macro. You could get some

[jjknknl]

Very helpful, ballardw, thanks!

[Kurt_Bremser]

When you do this:

 newname1 = %sysfunc(datepart(&dn));

the macro variable dn contains not a datettime value, but the NAME of a datetime variable. This, in the end, resolves in this code:

 newname1 = datepart("name_of_variable");

which will very obviously fail.

Instead, you propbably want to do this:

 newname1 = datepart(&dn);

which will now resolve to

 newname1 = datepart(name_of_variable);

and will work when the data step is finally executed.

Your next mistake is this:

rename newname1 = %sysfunc(tranwrd(&dn,&table._,%str()))

Since you do this in a macro loop, it will result in multiple renames for newname1; per definition, only the last of these renames will take effect, as a variable can be renamed only once. Not what you want.

Do this instead:

%sysfunc(tranwrd(&dn,&table._,%str())) = datepart(&dn);

which creates code for a new variable in each iteration of the macro loop. No rename necessary.

[jjknknl]

Very helpful, KurtBremser, thanks!