BookmarkSubscribeRSS Feed
HeatherNewton
Quartz | Level 8
If i have data in sas date9. Format eg 30nov2022, i want to change it text as 2022-11-30 so i can load to DB2 how do i do it?
Same for timestamp eg 30nov2022:11:30:22, how to convert to text as 2022-11-30.11.30.22 to load in DB2
12 REPLIES 12
Kurt_Bremser
Super User

SAS/ACCESS for DB/2 will send the data correctly to the database, regardless of display format.

If you communicate with the DB via textfiles, use the YYMMDD10. format in the PUT statement.

HeatherNewton
Quartz | Level 8
So you mean i can direct transfer sas dataset to db2. What is requirement on schema setting on db2?
SASKiwi
PROC Star

You can directly update or append DB2 tables from SAS datasets using SAS/ACCESS to DB2. SAS dates will correctly update DB2 dates. Based on the SAS variable types and formats used, SAS will translate these into valid new (if creating a DB2 table) or valid existing DB2 column definitions (if the DB2 table already exists).   

HeatherNewton
Quartz | Level 8
By sas/access do u mean i need sas/access interface to odbc license?
SASKiwi
PROC Star

Yes, if that is the SAS product you are accessing DB2 with.

Kurt_Bremser
Super User

Wait, wait, wait.

In your initial post, you asked how to transfer dates to DB/2.

Now you say you do not even have a connection to DB/2 yet?

Please start at the root of your issue when posting questions. Describe your current situation in detail, and what you want to achieve.

E.g. show us the log from PROC SETINIT, so we can see your licensed SAS products.

Setting up a direct connection to the DB requires the support of your DB/2 admins (they need to install the DB client on the SAS server, and the ODBC driver if you only have ACCESS to ODBC licensed).

HeatherNewton
Quartz | Level 8

I try a few methods to convert batch_date in date9. to yymmdd10. and cannot make it work

 

I tried 

informat batch_date yymmdd10.  nothing happened

 

try

put ( batch_date, yymmdd10.) 

put batch_date yymmdd10.

also no work

 

What am I missing in each case?

 

 

 

 

 

HeatherNewton
Quartz | Level 8

I have sas dataset where batch_date looks like 30NOV2022

now I need to convert the sas data set to csv and load into DB2

and in DB2 I want batch_date to look like 2022-11-30 instead

 

my questions is before I convert sas data set to csv, how do I convert format of batch_date from date9. to yyyymmdd10.??

Kurt_Bremser
Super User

In DB/2, dates are displayed in YYYY-MM-DD (ISO standard) per default.

To create a csv file with the correct format, do this.

/* test data */
data have;
input
  batch_date :date9.
  xxx $
;
format batch_date date9.;
datalines;
30nov2022 text
;

data _null_;
set have;
file "path_to_location/want.csv" dlm=",";
format
  batch_date yymmdd10.
;
if _n_ = 1 then put("batch_date,xxx"); * only if header line is needed;
put
  batch_date
  xxx
;
run;

The additional column xxx is used as an example for further columns you want to export

Tom
Super User Tom
Super User

@HeatherNewton wrote:

I try a few methods to convert batch_date in date9. to yymmdd10. and cannot make it work

 

I tried 

informat batch_date yymmdd10.  nothing happened

 

try

put ( batch_date, yymmdd10.) 

put batch_date yymmdd10.

also no work

 

What am I missing in each case?

 


INFORMATs are used to convert text into values.  Since you already have values in your dataset changing the INFORMAT associated with the variable will not have ANY impact at all.

 

FORMATs are used to convert values into text. 

 

So if you want your date values to print in the style YYYY-MM-DD then attach the YYMMDD10. FORMAT to the variable.

 

Double check the current format attached to your variable.  If it is DATE9 then changing the format to YYMMDD10. will make the dates print the way you want.  I have NO IDEA whether or not that will help with DB2.  You would need to ask your DB2 database administrator.

 

But if the current format attached to your variable is DTDATE9. then you have DATETIME values and not DATE values in the variable.  In that case using YYMMDD10. format to display them will not work.  You could use E8601DN10. format instead to print the datepart of a datetime value in the style YYYY-MM-DD.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 12 replies
  • 1454 views
  • 0 likes
  • 4 in conversation