BookmarkSubscribeRSS Feed
HeatherNewton
Quartz | Level 8

Hi

do we have any dataset in sas on demand for academics with columns

1. date e.g. 30NOV2022

2. datetime e.g. 30NOV2022:21:07:04

that I can use for testing??

 

what is common practice for converting datetime such that it can enter DB2?

I mean how to do it?

 

12 REPLIES 12
Kurt_Bremser
Super User

@HeatherNewton wrote:

Hi

do we have any dataset in sas on demand for academics with columns

1. date e.g. 30NOV2022

2. datetime e.g. 30NOV2022:21:07:04

that I can use for testing??

 

what is common practice for converting datetime such that it can enter DB2?

I mean how to do it?

 


As in your other thread, this depends on the tools available. Do you have SAS/ACCESS for DB/2 installed and licensed, or ACCESS to ODBC? Or do you need to create text files for later import into the DB?

SAS on Demand does not have any ACCESS modules for relational databases, so you can't test them there.

 

Regarding datasets for testing, such can easioy be created with a DATA step with DATALINES.

HeatherNewton
Quartz | Level 8

I need to convert sas dataset to csv and load csv into DB2

I want datetime looking like 30NOV2022:21:07:04 (datetime20.) in sas data set to look like 2022-11-30.21.12.57 in db2 

Kurt_Bremser
Super User

Are you sure you want dots to separate the time elements, and not colons?

Either roll your own format with PROC FORMAT, or create a temporary string:

temp_dt = catx(
  ".",
  put(datepart(dt),yymmdd10..),
  put(hour(timepart(dt)),z2.),
  put(minute(timepart(dt)),z2.),
  put(second(timepart(dt)),z2.)
);
HeatherNewton
Quartz | Level 8

how can I amend it such that I get output format like below:

 

2009-01-07-15.33.31

 

instead of  2009-01-07.15.33.31 from your code?

Tom
Super User Tom
Super User

You could use the E8601DT format and then convert the T and : into the separators you want.

data test;
  sas_datetime=datetime();
  db2_dtstring=translate(put(sas_datetime,e8601dt.),'-.','T:');
  format sas_datetime e8601dt.;
  put (_all_) (=/);
run;
sas_datetime=2023-06-15T11:27:39
db2_dtstring=2023-06-15-11.27.39
HeatherNewton
Quartz | Level 8

I tried this and got following error

 

LINE and COLUMN cannot be determined.

NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred

ERROR 180-322: Statement is not valid or it is used out of proper order.

 

what is wrong?

 

HeatherNewton
Quartz | Level 8

 

%macro conversion(filename);
options obs=10;
data test.&filename.;
set test.&filename.;
last_update_datetime_1=translate(put(last_update_datetime, e8601dt.),'-.','T:');
format last_update_datetime e8601dt.;run;
put(_all) (=/);
drop last_update_datetime;
rename last_update_datetime_1=last_update_datetime;
run;

filename exprt "/mpoint/test/&filename..csv" encoding="utf-8";
proc export data=test.&filename. outfile=exprt dbms=csv replace; run;
%mend;

I tried the above and got the following error,

180:LINE and COLUMN cannot be determind.

NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.

ERROR 180-322: Statement is not valid or it is used out of proper order.

and the above repeat three times

 

what has gone wrong??

 

Amir
PROC Star

Hi @HeatherNewton,

 

You appear to have an extra run statement that should be removed. Try changing:

 

format last_update_datetime e8601dt.;run;

to:

 

format last_update_datetime e8601dt.;

 

 

 

Kind regards,

Amir.

Tom
Super User Tom
Super User

You have a RUN statement in the middle of the data step.  If you get in the habit of no more than one statement per line those types of mistakes will be easier to spot.

 

Do you really have a variable named _ALL ?

Or did you want to print ALL of the variables?  If the latter then you need to use _ALL_ variable list.

Or better still once you get it to work you can remove the PUT statement completely as it is not really doing anything other than providing evidence that the conversion worked.

Reeza
Super User

IME having it stored as a datetime in SAS will allow it to upload properly to db2. 

Dates will not be uploaded correctly, they need to be converted to datetimes. 

Formats didn't matter, but as always, test it out on your system. 

 

Tom
Super User Tom
Super User

Hi

do we have any dataset in sas on demand for academics with columns

1. date e.g. 30NOV2022

2. datetime e.g. 30NOV2022:21:07:04

that I can use for testing??

 

Try checking the information about the variables and seeing if any of the datasets have both DATE and DATETIME variables.  You can use the FMTINFO() function to find out what type of format is attached to a variable.  It wants just the NAME of the format so use the output of PROC CONTENTS instead of the DICTIONARY.COLUMNS view.

proc contents data=sashelp._all_ noprint out=contents; run;
data want ;
do until (last.memname);
  set contents;
  by memname;
  if fmtinfo(format,'cat')='date' then datevar=name;
  if fmtinfo(format,'cat')='datetime' then dtvar=name;
end;
if not missing(datevar) and not missing(dtvar);
keep memname datevar dtvar ;
run;

proc print;
run;

Tom_0-1687782951091.png

So let's look at what it contains

Tom_1-1687783035866.png

 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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.

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
  • 12 replies
  • 1188 views
  • 1 like
  • 5 in conversation