Teradata Dates Modification

[new_sas_user_4]

Hi,

I am extracting data using Teradata where the dates are in  01JUN2019:00:00:06.000000 format.

- I want to extract data based on a date range (example below) such that I could use a macro like %let M1='2019-06-01' instead of date'2019-06-01'

- Also get the day number based on the date...( eg 01JUN2019:00:00:06.000000 would be 01 (day 1))

Example :

PROC SQL;
CONNECT TO TERADATA(USER="" PASSWORD="" SERVER="" mode=teradata DATABASE="");
create table A as
select region, dt,

day(cast(dt as timestamp)) as dt_number
from connection to teradata (select region, dt
from Teradata_table 
where T_date between date'2019-06-01' and date'2019-06-30'
)
;
disconnect from teradata;
quit;

Below is a sample data I created(though the date section is showing up as null when I run this...should the input statement be modified?)

data raw_Data;
length region $25;
input region $ dt;
datalines;
British Columbia 01JUN2019:00:00:06.000000
Alberta 02JUN2019:00:00:42.000000
ATlantic 03JUN2019:00:03:23.000000
Saskatchewan 01JUN2019:00:03:28.000000
Quebec 01JUN2019:00:05:06.000000
Manitoba 01JUN2019:00:05:06.000000
Quebec 01JUN2019:00:05:06.000000
Manitoba 01JUN2019:00:05:06.000000
Saskatchewan 01JUN2019:00:05:06.000000
Manitoba 01JUN2019:00:05:06.000000
Saskatchewan 01JUN2019:00:05:06.000000
;

Help please!

[Kurt_Bremser · Accepted solution]

So you have datetimes in Teradata.

On the SAS side, use datepart() to extract the date from a datetime, and day() to extract the day from a date.

But first, do a simple extract

PROC SQL;
CONNECT TO TERADATA(USER="" PASSWORD="" SERVER="" mode=teradata DATABASE="");
create table A as
select *
from connection to teradata (select region, dt
from Teradata_table 
where T_date between date'2019-06-01' and date'2019-06-30'
)
;
disconnect from teradata;
quit;

to see what kind of data you get from Teradata.

[Kurt_Bremser]

PS

day(cast(dt as timestamp)) as dt_number

is not valid SAS SQL syntax.

[new_sas_user_4]

I used day(datepart(dt)) as DOM and it worked 🙂

AND

data dates;
format reportdate CM_start CM_end YYMMDD10.;
reportdate='01JUN2019'd;
CM_start=intnx('month',reportdate,0,'B');
CM_end=intnx('month',reportdate,0,'E');
call symputx('CM_start',quote(put(CM_start,yymmdd10.),"'"));
call symputx('CM_end',quote(put(CM_end,yymmdd10.),"'"));
run;

for date range : dt >=date&cm_start and dt <=date&cm_end 

Thanks !! 🙂