topic Re: datetime in SAS Programming

Dataset with datetime - On Demand for Academics?

HeatherNewton — Thu, 15 Jun 2023 10:56:18 GMT

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?

Re: datetime

Kurt_Bremser — Sat, 13 May 2023 04:51:30 GMT

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

Re: datetime

HeatherNewton — Sat, 13 May 2023 05:19:59 GMT

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

Re: datetime

Kurt_Bremser — Sat, 13 May 2023 05:37:26 GMT

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

Re: datetime

HeatherNewton — Thu, 15 Jun 2023 10:17:39 GMT

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?

Re: datetime

Kurt_Bremser — Thu, 15 Jun 2023 10:49:00 GMT

Roll your own format with PROC FORMAT:

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

Re: datetime

Tom — Thu, 15 Jun 2023 15:28:26 GMT

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

Re: Dataset with datetime - On Demand for Academics?

Reeza — Thu, 15 Jun 2023 15:31:52 GMT

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.

Re: datetime

HeatherNewton — Mon, 26 Jun 2023 08:03:51 GMT

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?

Re: datetime

HeatherNewton — Mon, 26 Jun 2023 08:25:14 GMT

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

Re: datetime

Amir — Mon, 26 Jun 2023 08:51:33 GMT

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.

Re: datetime

Tom — Mon, 26 Jun 2023 11:58:13 GMT

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.

Re: Dataset with datetime - On Demand for Academics?

Tom — Mon, 26 Jun 2023 12:38:55 GMT

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