@Sathish_jammy:
The issue is two-pronged:
- Your values are datetimes but you want to format them as dates. The only canned format I know of that does that is E8601DN. However, it displays datetimes as YYYY-MM-DD (which to me is the only sane way to format dates); yet you want it as MM/DD/YYYY. If you okay with YYYY-MM-DD instead, then E8601DN. can be used directly in reformatting code. If not, you'll have to create a format formatting date times as MM/DD/YYYY via creating a function with proc FCMP and using it to compile a special format.
- Whatever decision is made above, use SQL to list the variables whose format begins wiht "DATETIME" and compose the corresponding ALTER statement.
First, suppose you're okay with YYYY-MM-DD (HAVE is just a test data set with datetime variables formatted as DATETIME22.3 and some other unrelated variables):
data have ;
dt = "10NOV2019:00:00:00"dt ;
date_a = dt + 123456789 ;
b_date = date_a + 123456789 ;
format dt date_a b_date datetime22.3 ;
retain otherc "otherc" othern1 111 othern2 222 ;
run ;
proc sql noprint ;
select catx (" ", name, "format=e8601dn10.") into :dtv separated by ","
from dictionary.columns
where libname = "WORK" and memname = "HAVE" and format eqt "DATETIME"
order varnum
;
alter table have modify &dtv ;
quit ;
If you run this code and look at HAVE, you'll see that the datetime variables are displayed as YYYY-MM-DD.
Now suppose that you still want the datetimes to be displayed as MM/DD/YYYY. In this case, you need to create a special format and then use it in SQL instead of E8601DN. This is one way to do it:
proc fcmp outlib = work.f.f ;
function dtfmt (dt) $ ;
return (put (datepart (dt), mmddyy10.)) ;
endsub ;
quit ;
option cmplib = work.f ;
proc format ;
value dtfmt (default=10) other=[dtfmt()] ;
run ;
proc sql noprint ;
select catx (" ", name, "format=dtfmt.") into :dtv separated by ","
from dictionary.columns
where libname = "WORK" and memname = "HAVE" and format eqt "DATETIME"
order varnum
;
alter table have modify &dtv ;
quit ;
Now if you look at HAVE, you'll see that the datetime variables appear formatted as MM/DD/YYYY.
EDIT: As @Tom has pointed out, you don't have to create the DTFMT. format using proc FCMP; you can create a picture format instead, for example:
proc format ;
picture dtfmt (default=10) low-high = '%0m/01/%Y' (datatype=datetime) ;
run ;
Kind regards
Paul D.
