Datepart function in proc sql

[Anita_n]

Dear all,

I have a question about using DATEPART in PROC SQL.

I have a table with timestamp like 25DEC20201:16:12:18 , informat Datetime28

I wish to use the following Alter  table function in proc sql to modify the column. I don't want to use a data step

PROC SQL;
Alter table test modify birthday=DATEPART(birthday) format =ddmmyy10.;
QUIT;

I know the syntax is wrong, but can any one help with the correct syntax? As I mentioned earlier I will prefer using the modify column to achieve the desired result.

Thanks

[Tom]

It is probably going to be a lot easier to just make a NEW dataset that has the new information.  Also why use SQL?

(Also why use either of DMY or MDY order for display dates? It will just confuse half of your audience)

data want ;
  set test;
  birthday=DATEPART(birthday) ;
  format birthday yymmdd10.;
run;

[Reeza]

MODIFY in a DB will not recreate the table, in SAS it still will so it doesn't matter if it's via modify or a new data set, the amount of work is the same behind the scenes. 

Since this is likely homework, you should check the SQL documentation for these statements and see how they're used. The section in orange is used to change the format. The section in purple is used to change the value.

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=sqlproc&docsetTarget=p0rd...

proc sql;
   title "World's Largest Countries";
   alter table sql.newcountries
      modify name char(60) format=$60.;
   update sql.newcountries
      set name='The United Nations member country is '||name;

It does also need a quit at the end, but that should explain how you need to format your syntax.

Spoiler

data stocks;
set sashelp.stocks;
date = dhms(date, 8, 4, 0);
format date datetime.;
run;

proc sql;
alter table stocks
modify date format=ddmmyy10.;
update stocks
set date = datepart(date);
quit;

proc contents data=stocks;
run;

[FreelanceReinh · Accepted solution]

Hello @Anita_n,

If your dataset test is very large and you just want to change the format of variable birthday from, e.g., datetime19. to something that looks like ddmmyy10., then, indeed, you may want to use the ALTER TABLE statement in PROC SQL (or the MODIFY statement of PROC DATASETS) so that the large dataset is neither read nor rewritten. But you would just alter the metadata rather than create a new variable using the DATEPART function.

proc format;
picture dtdmy
low-high='%0d/%0m/%Y' (datatype=datetime);
run;

proc sql;
alter table test
modify birthday format=dtdmy.;
quit;

I haven't found a suitable SAS-supplied format, therefore created a new one. Feel free to modify the format definition.

Edited after replies by @Anita_n and @Kurt_Bremser: Changed "other" to "low-high" in the format definition so as to avoid issues with missing values.

[Reeza]

That's a great idea! For most formats there's a DT version, ie DTDATE9 will apply the date9 format to a datetime variable. You can also control it sometimes by modifying the length, ie DATETIME7. is equivalent to Date7.

But the format of dd/mm/yy isn't commonly used, so none of those tricks work by default in the exact use case here.

[Anita_n]

@FreelanceReinhard
It worked quiet fine but the problem is that, it writes in missing fields ERROR as text. Is there any way to correct that?

[Kurt_Bremser]

You need to take care of missing values in the definition of the format:

data have;
input birthday datetime25.;
format birthday datetime25.;
datalines;
25DEC2020:16:12:18
.
;

proc format;
picture dtdmy
  . = "."
  other='%0d/%0m/%Y' (datatype=datetime)
;
run;

proc sql;
alter table have
modify birthday format=dtdmy.;
quit;

[Anita_n]

@Kurt_Bremser
Thanks, it worked

[FreelanceReinh]

@Anita_n: Sorry that I had overlooked that weakness in my original format definition. Thanks for pointing out that SAS creates the really unusual formatted value "     ERROR" (with five leading blanks) for missing values in this situation. And thanks to @Kurt_Bremser for providing the fix. Another way of fixing it is to use low-high instead of other in the PROC FORMAT step. As a bonus, this also handles special missing values (._, .a , ..., .z) in a satisfactory way. I've edited my original reply so that later readers of this thread won't run into those "ERROR" messages (in the output).

[Anita_n]

That also works perfectly, thankyou

[Anita_n]

@FreelanceReinhard; @KurtBrenser
Hi, there is a problem I realise. I can't open the table without having to run the code. I get this error message:
ERROR: Format dtdmy not found or couldn't be loaded for variable birthday. Is there any correction to this.

[Kurt_Bremser]

To have the format available, store it permanently where it is automatically picked up, e.g. in SASUSER:

proc format library=sasuser;

Where you store it depends largely on your SAS setup and usage.

[Anita_n]

Thanks alot for the reply

[FreelanceReinh]

My apologies, @Anita_n, your reply escaped my attention somehow. Thanks again to Kurt Bremser for stepping in.

Indeed, user-defined formats and informats that are permanently associated with variables in permanent datasets need to be available when working with these datasets. (System option NOFMTERR is a quick remedy, but of course cannot provide the missing [in]format definition.) The format catalogs searched for user-defined formats and informats are determined by the FMTSEARCH= system option, whose default value is

(WORK LIBRARY)

That is, after the (temporary) WORK format catalog of the current SAS session the catalog LIBRARY.FORMATS will be searched if the search in WORK.FORMATS was unsuccessful and the libref LIBRARY has been assigned. Since LIBRARY.FORMATS will always be searched (regardless of the FMTSEARCH setting), you can store the format definition in an arbitrary permanent library (e.g., your project library if the format is project-specific) and then use a LIBNAME statement to assign libref LIBRARY to that physical location.

Example:

Program containing the format definition:

libname proj 'D:\Projects\XYZ2021\sasdata';

...

proc format lib=proj;
picture dtdmy
low-high='%0d/%0m/%Y' (datatype=datetime);
run;

Program using the format itself or a dataset containing a variable that format DTDMY. has been associated with:

libname library 'D:\Projects\XYZ2021\sasdata';

...

Often there's already an existing libref for that physical path, say, plib. Then you can also use this in the LIBNAME statement:

libname plib 'D:\Projects\XYZ2021\sasdata';
libname library (plib);

... 

[Anita_n]

Thankyou very much