DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

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

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?


Accepted Solutions
Solution
‎03-06-2017 12:41 PM
Super User
Posts: 19,815

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

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. 

 

View solution in original post


All Replies
Frequent Contributor
Posts: 75

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

""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?

Contributor
Posts: 22

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

Posted in reply to nehalsanghvi
The base dataset is the "school_rp" seen in the syntax. Yes, I will post below with an attachment.
Solution
‎03-06-2017 12:41 PM
Super User
Posts: 19,815

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

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. 

 

Contributor
Posts: 22

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

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. 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 139 views
  • 1 like
  • 3 in conversation