topic Change all the date variable in MMDDYY10. format in SAS Programming

Change all the date variable in MMDDYY10. format

Sathish_jammy — Mon, 11 Nov 2019 11:47:49 GMT

Dear Experts,

I import the data into SAS from SQL Server (then analyzed) and Exported the dataset to Excel.

The date variable in the format of DATETIME22.3. (10NOV1947:00:00:000)

I know the way to convert for one/two variables. but my dataset has 50 more date variables.

I want to format all variables in MMDDYY10. at one shot.

Kindly suggest code to solve the date format.

Thanks in advance!

Re: Change all the date variable in MMDDYY10. format

PeterClemmensen — Mon, 11 Nov 2019 11:49:24 GMT

is there some common patter that identifies the 50 variables? Like a common prefix/suffix?

Re: Change all the date variable in MMDDYY10. format

Sathish_jammy — Mon, 11 Nov 2019 12:10:29 GMT

For five variables the suffix is like _date, the remaining date variable suffix is like _DT.

It'll be fine enough for me to convert only (suffix)_DT variables.

Re: Change all the date variable in MMDDYY10. format

PaigeMiller — Mon, 11 Nov 2019 12:59:48 GMT

Had the common naming been something like DATE1-DATE50, or even if all the variables had the same starting letters (example: they all begin with the letters DT_), this is simple; all you need is one FORMAT statement.

If they all end with _DATE or end with _DT (why are they named this way, anyway? is that how they come from the database?), then you'd probably need some sort of SQL solution, use SQL to create a macro variable that contains all fifty variable names. Sounds easier and more beneficial in the long run to go back and correct the naming.

proc sql noprint;
    select distinct name into :names from dictionary.columns where memname="YOURDATASETNAME"
    and libname="YOURLIBRARYNAME" and (scan(name,-1,'_')='DT' or scan(name,-1,'_')='DATE');
quit;

data new;
    set yourlibraryname.yourdatasetname;
    format &names mmddyy10.;
run;

Re: Change all the date variable in MMDDYY10. format

hashman — Mon, 11 Nov 2019 17:05:36 GMT

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

Re: Change all the date variable in MMDDYY10. format

Tom — Mon, 11 Nov 2019 16:31:55 GMT

Why would you use FCMP instead of a PICTURE format?

Re: Change all the date variable in MMDDYY10. format

hashman — Mon, 11 Nov 2019 16:35:44 GMT

@PaigeMiller :

Two points:

When datetime/timestamp variables come from MS SQL server, SAS auto-assigns a DATETIMEw.d format to them. Hence, there's no need to rely on variable names to determine which ones need to be reformatted, as it's enough to find out which ones have formats starting with "DATETIME".
Reassigning the formats the way you suggest will rewrite a potentially large data set. It's unnecessary since to reassign a format, it suffices to modify only the data set's descriptor via either proc DATASETS or proc SQL without touching the actual data.

Kind regards

Paul D.

Re: Change all the date variable in MMDDYY10. format

hashman — Mon, 11 Nov 2019 16:39:22 GMT

@Tom: I have no clue. It just occurred to me to do it this way, and it didn't occur to me to use a picture format ;). Thanks for the reminder.

Re: Change all the date variable in MMDDYY10. format

Tom — Mon, 11 Nov 2019 17:48:24 GMT

Do you want to convert the values from number of seconds (DATETIME values) to number of days (DATE values)? Or are you just looking to a way to display the datetime values with only the datepart?

Do you need to do this for ALL of the datetime values in your dataset? Or just some of them? If just some of them how can you identify which ones need to be changed?

Are you looking for a solution in SAS or in the exported Excel file?

Note that Excel, like SQL Server, does not really have a DATE data type. Only the DATETIME data type that you currently have.

Do you really need the values to display in the confusing month,day,year order? Why not just use the DTDATE format to display just the datepart of the datetime values?

Re: Change all the date variable in MMDDYY10. format

PaigeMiller — Mon, 11 Nov 2019 18:09:53 GMT

@hashman wrote:

@PaigeMiller :

Two points:

When datetime/timestamp variables come from MS SQL server, SAS auto-assigns a DATETIMEw.d format to them. Hence, there's no need to rely on variable names to determine which ones need to be reformatted, as it's enough to find out which ones have formats starting with "DATETIME".

Unless you only want the ones with variable names ending in DT and DATE, and not other similarly formatted variables whose name doesn't end with those strings. But neither your way nor my way might be completely definitive, depending on which exact 50 variables are needed.

Reassigning the formats the way you suggest will rewrite a potentially large data set. It's unnecessary since to reassign a format, it suffices to modify only the data set's descriptor via either proc DATASETS or proc SQL without touching the actual data.

Yes, PROC DATASETS would be a better approach.