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!
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.
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.
PS
day(cast(dt as timestamp)) as dt_number
is not valid SAS SQL syntax.
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 !! 🙂
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.