BookmarkSubscribeRSS Feed

Convert all datetime fields to date9

Started ‎12-11-2018 by
Modified ‎12-11-2018 by
Views 2,834

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;

 

Comments

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. 

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.

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

Version history
Last update:
‎12-11-2018 11:54 AM
Updated by:
Contributors

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Free course: Data Literacy Essentials

Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning  and boost your career prospects.

Get Started

Article Labels
Article Tags