Hi,
The good news is you can do both.
data _null_;
a = '2007-08-20';
b = tranwrd(a,'-','/');
put a= b=;
run;
a=2007-08-20 b=2007/08/20
In the macro environment you can indeed use
datastep functions in the %SYSFUNC(), however
the only difference you need to be aware of
is that in the datastep environment you do
have to quote the strings as you have done.
In the macro environment ALL arguments and
variables are already treated as text so
typically the only difference when using
%SYSFUNC is to remove quotemarks around the
text strings. See as follows that your
tranwrd can work just fine in macro-land.
%let a=%sysfunc(putn(%sysfunc(date()) -16, yymmdd10.));
%let b=%sysfunc(tranwrd(&a,-,/));
%put a=&a b=&b;
a=2007-08-20 b=2007/08/20
As far as using a translation on-the-fly
in SQL is to just use the same function
syntax as you did in the datastep above.
e.g. tranwrd(a,'-','/')
Hint: everything you can put in an expression
to the right of the equals sign(=) in a
datastep you can put in the expression in
SQL to the left of the 'as' within the SELECT
clause. i.e.
datastep x = 7 * (4 + 6)
(is the same as)
SQL 7 * (4 + 6) as x
datastep x = 'abc'||tranwrd(a,'-','/')
(is the same as)
SQL 'abc'||tranwrd(a,'-','/') as x
Kinda makes ya think that you can imagine
the '=' and 'as' as the same..... just
swap sides (i.e. destination and expression)
Hope this is helpful.
Mark Terjeson
Senior Programmer Analyst, IM&R
Russell Investments
Russell Investments
Global Leaders in Multi-Manager Investing
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of
***@MAILINATOR.COM
Sent: Wednesday, September 05, 2007 8:57 AM
To: SAS-***@LISTSERV.UGA.EDU
Subject: %sysfunc and date format
I need to convert a date value to 'yyyy/mm/dd' due to a proc sql where
clause that looks like
..and the_date >= ('2007/08/20')
to illustrate
data _null_;
a = '2007-08-20';
b = tranwrd(a,'-','/');
put a= b=;run;
a=2007-08-20 b=2007/08/20
tried this but it does not work
%let a=%sysfunc(putn(%sysfunc(date()) -16, yymmdd10.));
%let b=%sysfunc(tranwrd(&a,'-','/'));
%put &a &b;
ultimate I would want to do it directly in the proc sql statement,
something like
..and the_date >= (%sysfunc(putn(%sysfunc(date()) -16, yymmdd10.)))