Help - Proc SQL is changing the formatting of some date variables

[Jordan88]

Hi,

So I have a large administrative school dataset where I want to delete and replace the last 30 days of data. 

One varable called "date" which is date9 format (DDMONYYYY) is giving me some havoc during this process. For some reason, "date" in the base dataset gets changed to datetime20 format during the below proc sql command, and then the data for the "date" variable in the appending 30 days dataset is entered improperly because the two date formats no longer match. 

I really don't know why this would be occuring. 

I have double checked all the date formating up to the following SAS syntax, and the date format is Date9. in all tables up to this point. After this syntax is run, it becomes datetime20 and all dates from the last 30 days are entered as "01JAN1960:05:47:52".

proc sql;
            connect to odbc                                                                                                                                                
            (dsn="SCHEMA"
            user=USERNAME
            password=PASSWORD);
                  execute (                                                                                                                                                        
                  delete from "school_rp" WHERE                                                                                                                           
                  event_reported_datetime >= to_date(%BQUOTE('&last30days'),'DDMONYYYY')
                  ) by odbc;                                                                                                                                                  

      INSERT into schema.school_rp                                                                                                                              
      Select * from temp.school_rp_last30;                                                                                                                                 
                                                                                                                                                                       
      disconnect from odbc;                                                                                                                                            
quit;

Any ideas on what's happening?

[nehalsanghvi]

""date" in the base dataset gets changed to datetime20"  Which is the base dataset?

Can you give us some sample data from all tables you are referencing so we can see what might be going on?

[Jordan88]

The base dataset is the "school_rp" seen in the syntax. Yes, I will post below with an attachment.

[Reeza · Accepted solution]

Although it looks like a date, I suspect SAS interprets most dates from DB as datetime.

You don't specify what database type you're using, but some only store dates as datetime.

I would try the following, without any calculation, extract the variable that's 'changing' and see what format SAS see's it in. 

Or just treat it as a date time and go from there. 

[Jordan88]

Ahhh yes, you are absolutely right. Thank you. 

It is an Oracle database that it is saving it to, and it does say when you write to it that it does not save the SAS variable formatting.

So it is saving as a datetime variable, and then appending as a date variable. 

I guess I'll just format "date" the "last 30 days" dataset as datetime.