Date to text

[HeatherNewton]

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

[Kurt_Bremser]

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]

So you mean i can direct transfer sas dataset to db2. What is requirement on schema setting on db2?

[Kurt_Bremser]

The schema is set with the SCHEMA= option of the LIBNAME DB2 statement.

[SASKiwi]

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]

By sas/access do u mean i need sas/access interface to odbc license?

[SASKiwi]

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

[Kurt_Bremser]

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]

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?

[Kurt_Bremser]

Please read my post again, in particular the underlined part, and provide all the information requested.

[HeatherNewton]

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]

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]

---

@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.