Convert all datetime fields to date9

[#1]

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;

[ballardw]

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.

[Reeza]

There's a new option, format type that will return the date. 

[Tom]

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.

[tomrvincent]

@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.