Dataset with datetime - On Demand for Academics?

[HeatherNewton]

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?

[Kurt_Bremser]

---

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

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]

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]

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?

[Kurt_Bremser]

Roll your own format with PROC FORMAT:

picture mydt
  low - high='%Y-%0m-%0d-%0H.%0M.%0S' (datatype=datetime)
;

[Tom]

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]

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]

%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]

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]

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]

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]

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;

So let's look at what it contains