CODEWITHSUNDEEP
sqlsas
John L.
John L.

Reputation: 1

How to convert/update dates from YYYY-MM-DD to DDMMMYYYY format?

I'm relatively new to coding, but have started to pick it up at my new job. Currently I'm trying to get two columns in two different tables to have the same date format. The first is in DDMMMYYYY format (ie. 01Jan2019) while the second is in YYYY-MM-DD format (ie. 2019-01-01). I would like to change 2nd to the 1st format, but I haven't seen any advice on the net for this. Any help would be greatly appreciated as the final goal is to join these two tables on this date column.

I'm assuming the form of the code will look something like this?

update table2
set date = ???????;

Upvotes: 0

Views: 2082

Answers (3)

Tom
Tom

Reputation: 51566

In SAS a FORMAT is just instructions for how to display the value. So if you have one variable with the DATE9. format specification attached and another with the YYMMDD10. format attached the actual value stored does not change.

You can use a FORMAT statement to change what display format to use with a variable. You could just do that at the point where you are displaying the data. Say in a PROC step.

proc print data=a ;
   format date_vara yymmdd10.;
run;
proc print data=b ;
   format date_varb yymmdd10.;
run;

Or you can attach the format in the dataset definition. Then by default SAS will use that to format.

data new;
  set a b ;
  format date_vara date_varb yymmdd10.;
run;

You can even modify the dataset's metadata to change what format is attached to the variable.

proc datasets lib=work nolist;
  modify a;
  format date_vara yymmdd10.;
  run;
quit;

PS If you want to add date literals (constants) to your SAS code then you need to always use strings that are in a style that the DATE informat can read. where date_varb >'01JAN2019'd ; No matter what display format is attached to the variable.

Upvotes: 1

Reeza
Reeza

Reputation: 21264

Assuming the type of the variable is numeric with a date format, this can be as simple as changing the format applied. If it's a character variable you need to do a type conversion.

You can apply a format in a data step or modify the existing format using PROC DATASETS. See examples of each below.

*change format as part of a data step;
data ibm;
set sashelp.stocks;
format date yymmddd10.;
run;

proc print data=ibm (obs=5);
run;

*change format without modifying data set;
proc datasets lib=work nodetails nolist;
modify ibm;
format date worddate.;
run;

proc print data=ibm (obs=5);
run;

Upvotes: 0

Ivan
Ivan

Reputation: 373

The column of 'DateTime' type can't be formatted in a specific way. It's very possible that one or both of the columns are either of 'varchar' or 'nvarchar' type.

To use them as proper 'DateTime' columns you may add a new column of 'DateTime' type and then copy to it values from the old 'varchar' column as 'DateTime'.

Upvotes: 1

Related Questions