topic Passing a variable as an argument to a function ??? in SAS Procedures

Passing a variable as an argument to a function ???

wjugon85 — Sat, 02 Mar 2013 12:00:29 GMT

Hi All,

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) :

E.g.

Dataset	Dates
X1	DT1, DT2, DT3
X2	DT1, DT3, DT4, DT5
X3	DT1, DT2
X4	DT1

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;

data testX1;

set X1;

mindt = min(dates);

run;

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.

dates testX1;

set X1;

mindt = min(DT1,DT2,DT3);

run;

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.

Kind regards,

Re: Passing a variable as an argument to a function ???

Tom — Sat, 02 Mar 2013 15:06:20 GMT

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.

data metadata;

input ds $ dates $30. ;

cards;

X1 DT1, DT2, DT3

X2 DT1, DT3, DT4, DT5

X3 DT1, DT2

X4 DT1

;;;;

filename code temp;

data _null_ ;

file code ;

set metadata ;

put 'data test' ds ';'

/ ' set ' ds ';'

/ ' mindt = min(.,.,' dates ');'

/ 'run;'

;

run;

*** GENERATED PROGRAM **** ;

data testX1 ;

set X1 ;

mindt = min(.,.,DT1, DT2, DT3 );

run;

data testX2 ;

set X2 ;

mindt = min(.,.,DT1, DT3, DT4, DT5 );

run;

data testX3 ;

set X3 ;

mindt = min(.,.,DT1, DT2 );

run;

data testX4 ;

set X4 ;

mindt = min(.,.,DT1 );

run;

Re: Passing a variable as an argument to a function ???

wjugon85 — Sat, 02 Mar 2013 16:13:36 GMT

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 **;

data dsnames(keep=memname);

set sashelp.vtable;

where libname = 'RAWSDTM';

run;

** GET A LIST OF ALL DATE9. VARIABLES FROM THE DATASETS IN LIBRARY **;

data dscols(keep=memname name);

set sashelp.vcolumn;

where libname = 'RAWSDTM' and format='DATE9.';

run;

** TRANSPOSE SO THAT 1 ROW PER DATASET CONTAINING COLUMNS WITH VARIABLE NAMES **;

proc transpose data=dscols out=dscolst(drop = _NAME_ _LABEL_);

var name;

by memname;

run;

** 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);

options missing='';

merge dsnames dscolst;

attrib date length=$300.;

by memname;

date=catx(',', of col:);

run;

** MACRO TO BE USED IN CALL EXECUTE TO CUT DATA BY PASSING IN DATASET FROM META DATASET ABOVE **;

%macro cut(dsname);

data &dsname._dates(keep=memname date);

set all;

where memname="&dsname";

run;

proc sql;

create table &dsname as select

a.*, b.date from

RAWSDTM.&dsname a left join &dsname._dates b

on a.form=b.memname;

quit;

data ANA.&dsname ANA.&dsname._cut;

set &dsname;

attrib mindt format=date9.;

mindt = min(.,.,'date');

if datepart(mindt) <= &cutdt then output ANA.&dsname;

else output ANA.&dsname._cut;

run;

%mend cut;

** EXECUTE THE MACRO VIA CALL EXECUTE **;

data _null_;

set all;

call execute ('%cut('||memname||')');

run;

All works fine if remove the min statement that I am having issues with...

Re: Passing a variable as an argument to a function ???

Tom — Sat, 02 Mar 2013 16:33:50 GMT

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);

....

Re: Passing a variable as an argument to a function ???

Tom — Sat, 02 Mar 2013 17:21:14 GMT

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;

set &libref..&dsname;

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;

run;

%mend 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);

set sashelp.vcolumn;

by memname ;

where libname = %upcase("&libref")

and format =: 'DATE'

;

dates=catx(' ',dates,name);

end;

call execute (cats(

'%cut(dsname=',memname

,",cutdt=&cutdt"

,',varlist=',dates

,",inlib=&libref"

,',outlib=ANA'

,')'))

;

run;

Re: Passing a variable as an argument to a function ???

wjugon85 — Mon, 04 Mar 2013 00:27:59 GMT

Hi TOm,

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

-----------

49 388

200

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.

Kind regards,

Re: Passing a variable as an argument to a function ???

Tom — Mon, 04 Mar 2013 03:54:18 GMT

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.