BookmarkSubscribeRSS Feed
hellind
Quartz | Level 8

Currently the code below will output the results to the SAS dataset WORK.UOBS_RMTL.

The field SUBMISSION_DATE in WORK.UOBS_RMTL is in text. Is there anyway for SAS to auto pick up as date format?

I prefer not to run another data step to convert text to date using INPUT.

proc sql;

CONNECT to ODBC as oradb (datasrc=UOBS_PROD user=&SUser. password=&SPwd.);

%put &sqlxmsg;

   CREATE TABLE WORK.UOBS_RMTL (COMPRESS=YES) AS

  SELECT *

  FROM connection to oradb 

       (

  SELECT

   CA_NUMBER

  , CONVERT(varchar(10), SUBMISSION_DATE, 126) AS SUBMISSION_DATE

  FROM [DRLPP].[dbo].[SML_CA]

) ;

%put &sqlxmsg;

DISCONNECT from oradb;

quit;

run;

4 REPLIES 4
hellind
Quartz | Level 8

SAS SQL Date formatting - Stack Overflow

Someone suggested like this:

proc sql;
  create table data.test as
  select ID, INPUT(CREATION_DATE,datetime20.) AS CREATION_DATE
  from connection to odbc
  ( select ID, DATE AS CREATION_DATE
  from maintable );
quit;

If I have 12 date columns, it means I have to define 12 times. Quite time-consuming.Smiley Sad

Am looking for some lazy coding.

esjackso
Quartz | Level 8

I think the only ways that SAS "guesses" at variables types is through proc import or the import wizard.

You could do the conversions in a data step using arrays and a do loop to save a little on the typing.

EJ

tfearn
Calcite | Level 5

I had this problem in connecting to newer SQL server database and the solution was to get an updated version of the odbc interface. The problem there was that the database had a new date field datatype. In the old odbc interface the field was processed as a text field and in the new one it was processed as sas datetime field.

Marina
Calcite | Level 5

In the sample code you provided you are converting data type from date to character. If you just select the field you need without conversion:

         SELECT    CA_NUMBER,

                          SUBMISSION_DATE

          FROM ...

you will have SUBMISSION_DATE as date if it is date in database.

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
  • 4 replies
  • 1454 views
  • 0 likes
  • 4 in conversation