If you have a dataset with a bunch of datetime fields (that you brought in from an external database, for example) and you want to work with them date9, this will generate a view of that dataset for you.
%macro date9(lib,dsn);
/* this macro will create a view with datepart function applied to all datetime fields */
data vars;
length name $ 50 type $ 1
format informat $ 50 label $ 50;
drop dsid i num rc;
dsid=open("&lib..&dsn","i");
num=attrn(dsid,"nvars");
do i=1 to num;
name=varname(dsid,i);
type=vartype(dsid,i);
format=varfmt(dsid,i);
informat=varinfmt(dsid,i);
label=varlabel(dsid,i);
length=varlen(dsid,i);
position=varnum(dsid,name);
output;
end;
rc=close(dsid);
run;
proc sql;
select
case
when format contains 'DATETIME' then 'datepart('||trim(name)||') as '||trim(name)||' format=date9.'
else name
end
as name
into :names1- from vars;
%let recs=&sqlobs;
proc sql;
create view &lib..vw_&dsn
as select &names1
%do i=2 %to &recs;
, &&names&i
%end;
from &lib..&dsn
;
%mend;
You need to search for more format names such as DTDATE, DTMONYY, DTWKDATX, DTYEAR,DTYYQC, MDYAMPM; the national language equivalents such as: NLDATMDT, NLDATMAP, NLDATM, NLDATML, NLDATMM, NLDATMMD; and a whole slew of 8601 formats.
There's a new option, format type that will return the date.
Use the FMTINFO() function with 'cat' as the information type to test if the format attached to a value is of type DATETIME.
You can use the SASHELP.VFORMAT view to find a list of format names. When I tried it there were 75 DATETIME formats listed there.
@ballardw I don't need to, but if someone else has a need to, they can certainly expand it as they see fit.
@Reeza Do you have a link for that? I was unable to find anything like 'option format type'.
@TomThanks. I only needed to take care of DATETIME22.3 right now but that could be helpful to others.
Catch the best of SAS Innovate 2025 — anytime, anywhere. Stream powerful keynotes, real-world demos, and game-changing insights from the world’s leading data and AI minds.
The rapid growth of AI technologies is driving an AI skills gap and demand for AI talent. Ready to grow your AI literacy? SAS offers free ways to get started for beginners, business leaders, and analytics professionals of all skill levels. Your future self will thank you.