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;
... View more
