BookmarkSubscribeRSS Feed
BETO
Fluorite | Level 6

hi I have a date time field  which I do a date part to convert it to date it works but when I try to join it to another table an bring in the converted date it changes to  01/01/00 on all the dates .... How can I keep date being date?

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Post your code, at a guess the variables format is still date/time even though you only put a date in it. 

data example_date;

     set have;

     format new_date date9.;

     new_date=datepart(old_date);

run;

BETO
Fluorite | Level 6

TThanks for responding

here is my code

data table;

set table(keep =active acctnumber flm slm datetime state city zip);

format date date9.;

date= datepart (date time);

run;

it works I get my date only but when i try to join to another table it converts to 01/01/00 the table is coming from a sql table don't know if that makes. Difference one more thing I sort my original table after I did the datepart an it change it to 01/01/00 one the on the original table thanks again

Reeza
Super User

Usually SQL stores dates as date time.

You need to show your code - the merge that's causing the issue.

BETO
Fluorite | Level 6

THe table looks like this

proc sql;

create Table audit as

(

select

A.id,

a.owner,

b.active2,

b.date,

c.active1

from table a, table b, table c

where a.id = b.id

and a.id=c.id

);

quit;

Reeza
Super User

It appears that Table B has a datetime variable, try using dtdate9. format and see how it appears.

proc sql;

create Table audit as

select

A.id,

a.owner,

b.active2,

b.date format=dtdate9.,

c.active1

from table a, table b, table c

where a.id = b.id

and a.id=c.id

;

quit;

BETO
Fluorite | Level 6

It change date to 31dec1959 to all of them

BETO
Fluorite | Level 6

I Thought I could be slick an convert date to character an than join table dive I just need to match another character date an even after I change it to date character it still change format to 01/01/00

BETO
Fluorite | Level 6

I Think I found the error  in mytable  I have a date time field but when I enter no value in it this date 01/01/00 stored automatically into it

i want to keep the field null where there is no value

i notice that my issue is the same. The col has more blanks than entries since it's the date of deinstalled  an where there is no date it defaults to 0/01/00 I would like to have it empty or null

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 8 replies
  • 1102 views
  • 3 likes
  • 3 in conversation