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!
... View more