BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
new_sas_user_4
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

3 REPLIES 3
Kurt_Bremser
Super User

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.

new_sas_user_4
Obsidian | Level 7

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 !! 🙂

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 3 replies
  • 2792 views
  • 0 likes
  • 2 in conversation