BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BU2B
Calcite | Level 5

Hey all,

I'm trying to append two datasets, the base has a date format of DATETIME9. and the data in has a format of DATE9.  They look identical in the datasets but when I append and export to a CSV I get 01JAN1960 for all the data in the input dataset.  How do I get the dates to match in the appended dataset and the exported CSV file?

Steve

1 ACCEPTED SOLUTION

Accepted Solutions
Anotherdream
Quartz | Level 8

Hi, so the problem is Datetime9. is a format for a DATETIME field.  DATETIMES are different than dates, because datetimes are the NUMBER OF SECONDS since 1/1/1960 : 12 AM.   while Dates are the number of DAYS since 1/1/1960. So by definition if the value in one of your fields was 5 (in datetime) it would convert it to 1/1/1960:00:00:05.000   while the other would be 1/6/1960.

The reason they LOOK the same (they're aren't) is because the datetime9, format isn't long enough to show all of your TIME data (only the date).  Please try to re-format the variable as Datetime22.3 to see the differences.

So in short, you have two DIFFERENT variables, and you will need to convert one of them before combining them and outputting them!

If you need help with this step, please attach some sample data and output you would like and I can code it up in a minute or two for ya

View solution in original post

5 REPLIES 5
Community_Help
SAS Employee

Hi Steve, I just moved your note to a technical community where it should get some more traction.. you might want to also try searching in the communities as well..

BU2B
Calcite | Level 5

OK thanks will do!

Anotherdream
Quartz | Level 8

Hi, so the problem is Datetime9. is a format for a DATETIME field.  DATETIMES are different than dates, because datetimes are the NUMBER OF SECONDS since 1/1/1960 : 12 AM.   while Dates are the number of DAYS since 1/1/1960. So by definition if the value in one of your fields was 5 (in datetime) it would convert it to 1/1/1960:00:00:05.000   while the other would be 1/6/1960.

The reason they LOOK the same (they're aren't) is because the datetime9, format isn't long enough to show all of your TIME data (only the date).  Please try to re-format the variable as Datetime22.3 to see the differences.

So in short, you have two DIFFERENT variables, and you will need to convert one of them before combining them and outputting them!

If you need help with this step, please attach some sample data and output you would like and I can code it up in a minute or two for ya

Tom
Super User Tom
Super User

So if you APPEND two datasets where they each have the same variable name, but they are using different FORMATS to display the values then the new dataset will use the first FORMAT that it sees.  You can override this by adding you own FORMAT statement.  But remember that changing the format attached to a variable does not change the values stored in the variable, just how SAS displays the value when printing it.

Try this example:

data a;

  src='A';

  date = today();

  format date date9.;

  put src= date=;

run;

data b;

  src='B';

  date = datetime();

  format date dtdate9. ;

  put src= date=;

run;

data c;

set a b ;

put src= date=;

run;

data d;

set b a ;

put src= date=;

run;

The answer is to convert the data so that both sets of values are using the same units so that you can apply one format.  For example you could use the DATETIME() function to convert the datatime values to date values.  (Or use the DMHS() function to convert date values to datetime values).

data both ;

set a (in=ina) b (in=inb);

if inb then date = datepart(date);

format date date9.;

run;

Vish33
Lapis Lazuli | Level 10

Try with informat and format  for the base data.,

data base;

set base;

informat date_variable datetime9. ;

format date_variable date9.;

run;

/* now append two datasets */

proc append base=base data=data force;

run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 2343 views
  • 0 likes
  • 5 in conversation