03-02-2013 07:00 AM
I have an issue, maybe small but something that has bugged me through experimentation.
I have a data set which contains a variable which contains a list of date variables concatenated (I originally got all date variables using the vcolumns options for each data set and stored them in a variable) :
|X1||DT1, DT2, DT3|
|X2||DT1, DT3, DT4, DT5|
I then want to have a min function to find the minimum of all the date fields in the dataset:, using dataset X1 as an example;
mindt = min(dates);
Now this falls over as states it doesn't contain enough arguments. I can see the issue in that "dates" is only a single variable. How can I modify this to read the contents of the variables as the argument e.g.
mindt = min(DT1,DT2,DT3);
This works, but as I have to do this dynamically for a number of datsets, I tried storing the values as a variable and read this variable in, any suggestion please?
I assume a do loop, or create a macro variable but unsure as to how this works as to passing in a function.
03-02-2013 10:06 AM
How are you using the information? Looks to me like you have a table with metadata that you want to use to generate code. So when you generate the code put out the value of the variable instead of its name. To get the MIN() function to not complain about too few arguments add in addition missing values as arguments.
input ds $ dates $30. ;
X1 DT1, DT2, DT3
X2 DT1, DT3, DT4, DT5
X3 DT1, DT2
filename code temp;
data _null_ ;
file code ;
set metadata ;
put 'data test' ds ';'
/ ' set ' ds ';'
/ ' mindt = min(.,.,' dates ');'
*** GENERATED PROGRAM **** ;
data testX1 ;
set X1 ;
mindt = min(.,.,DT1, DT2, DT3 );
data testX2 ;
set X2 ;
mindt = min(.,.,DT1, DT3, DT4, DT5 );
data testX3 ;
set X3 ;
mindt = min(.,.,DT1, DT2 );
data testX4 ;
set X4 ;
mindt = min(.,.,DT1 );
03-02-2013 11:13 AM
Thank you Tom,
this does indeed generate code, here is the issue I have in that I am using this call within a call execute statement. When I run this, it tells me the variable ' date ', is not numeric, again true as this contains just a meta list of the variables. Here I have attached my program:
** ASSIGN A CUT DATE **;
%let cutdt= "19FEB2013"d ;
** GET A LIST OF ALL DATASETS IN LIBRARY **;
where libname = 'RAWSDTM';
** GET A LIST OF ALL DATE9. VARIABLES FROM THE DATASETS IN LIBRARY **;
data dscols(keep=memname name);
where libname = 'RAWSDTM' and format='DATE9.';
** TRANSPOSE SO THAT 1 ROW PER DATASET CONTAINING COLUMNS WITH VARIABLE NAMES **;
proc transpose data=dscols out=dscolst(drop = _NAME_ _LABEL_);
** MERGE DATES ON TO DATASET AND CONCATENATE EACH DATE VARIBALE INTO A SINGLE DATE VARIABLE TO BE USED IN CALCULATIONS **;
data all(keep=memname date);
merge dsnames dscolst;
attrib date length=$300.;
date=catx(',', of col;
** MACRO TO BE USED IN CALL EXECUTE TO CUT DATA BY PASSING IN DATASET FROM META DATASET ABOVE **;
data &dsname._dates(keep=memname date);
create table &dsname as select
a.*, b.date from
RAWSDTM.&dsname a left join &dsname._dates b
data ANA.&dsname ANA.&dsname._cut;
attrib mindt format=date9.;
mindt = min(.,.,'date');
if datepart(mindt) <= &cutdt then output ANA.&dsname;
else output ANA.&dsname._cut;
** EXECUTE THE MACRO VIA CALL EXECUTE **;
call execute ('%cut('||memname||')');
All works fine if remove the min statement that I am having issues with...
03-02-2013 11:33 AM
So what you want to do in your macro is pull the VALUE of the date variable into a macro variable. Then replace 'date' with reference to that macro variable. You could probably simplify your whole process if instead of even building the concatenated variable list you just left them as rows in a table. Then the key parts of your code would look something like below. (NOTE: I have used space as the delimiter and the OF keyword in the MIN() function call instead of dealing with commas. )
%local varlist ;
select name into :varlist separated by ' ' from ....
mindt = min(.,of &varlist);
03-02-2013 12:21 PM
Here is a much more compact way. I made the CUT macro accept the list of date variables (and other things) as inputs. Plus it is easier to build your list of dataset names and the associated list of date variables using a single data step. You also might want to search for other formats than just DATE (such as MMDDYY, DDMMYY, YYMMDD) depending on how much control you have over the source datasets.
Note that DATEPART() function is something you apply to DateTime variables. If you apply it to a Date variable you will get wrong result. If you want to find DateTime variables then perhaps you could generate DATEPART(dtvar) into your DATES string. Example: dates= 'DOB VISITDT datapart(EVENTTS)'
%macro cut(dsname,cutdt,varlist=,inlib=,outlib=) ;
* Split dataset based on cutoff date ;
data &outlib..&dsname &outlib..&dsname._cut;
attrib mindt format=date9. label='Minimum value of all date variables';
mindt = min(of . &varlist);
if mindt <= &cutdt then output &outlib..&dsname;
else output &outlib..&dsname._cut;
%let cutdt= "19FEB2013"d ;
%let libref = RAWSDTM ;
* Get list of DATE variables ;
* Call CUT macro for each dataset ;
data datevars(keep=memname dates);
length memname $32 dates $300 ;
do until (last.memname);
by memname ;
where libname = %upcase("&libref")
and format =: 'DATE'
call execute (cats(
03-03-2013 07:27 PM
Thanks for the above, looks good and agree is more efficient, I do seem to have a problem calling the cutoff date within the call execute statement, I cannot seem to fathom why?
NOTE: Line generated by the macro variable "CUTDT".
106 ",cutdt="19FEB2013" d
NOTE 49-169: The meaning of an identifier after a quoted string may change in a future SAS release. Inserting white space between a quoted string and the succeeding
identifier is recommended.
ERROR 388-185: Expecting an arithmetic operator.
ERROR 200-322: The symbol is not recognized and will be ignored.
I keep getting this error when running the above as it is. Any suggestion, UI have tried playing around but to no avail.
03-03-2013 10:54 PM
That is just an issue with using the same quoting character outside and inside.
You could change the code in the CALL EXECUTE to use single quotes or change the value used in defining CUTDT macro variable to use single quotes.