topic Re: Date to text in SAS Programming

Date to text

HeatherNewton — Sun, 07 May 2023 12:58:31 GMT

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

Re: Date to text

Kurt_Bremser — Sun, 07 May 2023 18:55:01 GMT

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.

Re: Date to text

HeatherNewton — Sun, 07 May 2023 15:24:58 GMT

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

Re: Date to text

Kurt_Bremser — Sun, 07 May 2023 18:54:13 GMT

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

Re: Date to text

SASKiwi — Sun, 07 May 2023 20:11:07 GMT

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

Re: Date to text

HeatherNewton — Mon, 08 May 2023 23:15:11 GMT

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

Re: Date to text

SASKiwi — Mon, 08 May 2023 23:41:42 GMT

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

Re: Date to text

Kurt_Bremser — Tue, 09 May 2023 04:37:47 GMT

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

Re: Date to text

HeatherNewton — Sat, 13 May 2023 04:33:21 GMT

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?

Re: Date to text

Kurt_Bremser — Sat, 13 May 2023 04:45:39 GMT

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

Re: Date to text

HeatherNewton — Sat, 13 May 2023 05:14:17 GMT

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

Re: Date to text

Kurt_Bremser — Sat, 13 May 2023 17:34:41 GMT

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

Re: Date to text

Tom — Sat, 13 May 2023 14:16:12 GMT

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