DATA Step, Macro, Functions and more

SAS date and teradata dates

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

SAS date and teradata dates

I have below dataset.

 

 

date
01jan17
01feb17
01jun17
30jun17
01apr17
01mar17

I created new macro to split the dataset based on dates.

 

%macro date_loop(start,end);
%let start=%sysfunc(inputn(&start,anydtdte9.));
%let end=%sysfunc(inputn(&end,anydtdte9.));
%let dif=%sysfunc(intck(month,&start,&end));
%do i=0 %to &dif;
%let date=%sysfunc(putn(%sysfunc(intnx(month,&start,&i,b),yymmn6.));
proc sql;
create table new&date. as select 8 from new where date=&start.; quit;
%put &date &dif;
%end;
%mend;

%date_loop;

The above code is working fine in SAS EG but its not working it it is accessing teradata dates.

 

For example; dates for start is 2017-01-01 and end 2017-06-01.

 

How to handle date function in above code it is teradata.


Accepted Solutions
Solution
‎05-05-2018 04:37 AM
Super User
Super User
Posts: 9,427

Re: Find difference between two teradata dates in sas

Posted in reply to suresh123

Doesn't seem to have anything to do with teradata?  You have created two macro variables - which are strings - this string data is used in the intck function which is expecting two numeric date items, hence you get the error that the strings you pass are not numeric.  Now you could do if you changed your date format as only date9 is valid for literals:

%let a='01JAN2017'd;

So that will when put into the intck be treated as a date literal and be converted to a numeric date value.  It is however rarely a good idea to be putting - dates or quotes into macro variables, adn then using date functions on strings.  If you want the days between:

data _null_;
  call symputx('days',intck('month',input('2017-01-01',yymmdd10.),input('2017-12-01',yymmdd10.)));
run;

Really need more info on what your doing, is this pass through to the database, if so teradata has date compare functions does in not?

View solution in original post


All Replies
Valued Guide
Posts: 560

Re: SAS date and teradata dates

Posted in reply to suresh123

Most database store date values as Datetime, so first check how your date values from teradata are returned in SAS. If your teradata table is very large just for test get only few rows (Obs=10). Then if it is datetime then you need to change your where clause to DATEPART(teradata_datetime)=&start. 

Thanks,
Suryakiran
Super User
Posts: 23,321

Re: SAS date and teradata dates

Posted in reply to suresh123

Dates need to be specified in the date literal format, not YYMMN6 like you're requesting UNLESS the field in Teradata is a character. 

So make sure your macro variable resolves to:

 

'01Jun2017'd or something like that. The quotes are required, you can either include them in the macro variable or in the code when resolving the macro variable.

 

%let date=%sysfunc(putn(%sysfunc(intnx(month,&start,&i,b)));

Occasional Contributor
Posts: 19

Re: SAS date and teradata dates

[ Edited ]

If that is the case, how to find difference between two teradata dates in sas like 

 

%let a='2017-01-01';

%let b='2017-12-01';

Super User
Posts: 23,321

Re: SAS date and teradata dates

Posted in reply to suresh123

Are you using SQL Pass through or PROC SQL. If you're using SQL Pass through you have to specify the dates in the tera data format. If you're using PROC SQL you need to use SAS date literals. @kiranv_ answer is assuming SQL Pass through. 

 

If you want to find the difference you'd first have to remove quotes, read it in as dates, then take the difference using INTNX. Do you have control over how the macro variables are created? If you do, the simplest solution is to specify it in Date9 literal format.

 

 

Occasional Contributor
Posts: 19

Find difference between two teradata dates in sas

Posted in reply to suresh123

Iam completely new to teradata dates.

 

I have two teradata dates and trying to the no of months between them

 

%let a='2017-01-01';
%let b='2017-12-01';


%let days = %sysfunc(intck(month,&a.,&b.));
%put days;

 

Below is the error

ERROR: Argument 2 to function INTCK referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.
ERROR: Argument 3 to function INTCK referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.
ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list.  Execution of %SYSCALL statement or %SYSFUNC 
    or %QSYSFUNC function reference is terminated.
PROC Star
Posts: 1,592

Re: Find difference between two teradata dates in sas

Posted in reply to suresh123

HI @suresh123 Good morning, I have feeling you probably haven't read what @Reeza and @KurtBremser  explained as a response to the thread  https://communities.sas.com/t5/Base-SAS-Programming/SAS-date-issue/m-p/459794 initiated by you. If you get the grasp of their explanation, you will know why. Please refer to the thread again.

Occasional Contributor
Posts: 19

Re: Find difference between two teradata dates in sas

Posted in reply to novinosrin
Thanks novinosrin. I will look into it.
Frequent Contributor
Posts: 102

Re: Find difference between two teradata dates in sas

Posted in reply to suresh123

You could acknowledge help as a matter of basic courtesy when people take their time to offer their help, which you haven't 

Occasional Contributor
Posts: 19

Re: Find difference between two teradata dates in sas

I am really sorry for that. I am still trying to figure out to get difference between two dates using sas for teradata. I am new to sysfunc concept
Super User
Posts: 23,321

Re: Find difference between two teradata dates in sas

Posted in reply to suresh123

@suresh123 read the answer I just posted in the previous thread. I'll merge these threads since the topic is the same and its confusing now.

Solution
‎05-05-2018 04:37 AM
Super User
Super User
Posts: 9,427

Re: Find difference between two teradata dates in sas

Posted in reply to suresh123

Doesn't seem to have anything to do with teradata?  You have created two macro variables - which are strings - this string data is used in the intck function which is expecting two numeric date items, hence you get the error that the strings you pass are not numeric.  Now you could do if you changed your date format as only date9 is valid for literals:

%let a='01JAN2017'd;

So that will when put into the intck be treated as a date literal and be converted to a numeric date value.  It is however rarely a good idea to be putting - dates or quotes into macro variables, adn then using date functions on strings.  If you want the days between:

data _null_;
  call symputx('days',intck('month',input('2017-01-01',yymmdd10.),input('2017-12-01',yymmdd10.)));
run;

Really need more info on what your doing, is this pass through to the database, if so teradata has date compare functions does in not?

PROC Star
Posts: 503

Re: SAS date and teradata dates

[ Edited ]
Posted in reply to suresh123

You need to create macro variable as shown below. because for teradata to understand date it needs to be in quote as 'YYYY-MM-DD'. An example is shown below.

 

%let start_dt = '2017-07-01';
%let end_dt = '2017-07-31';
proc sql;
connect to teradata (server=myserver user=myuserid pw=mypass);
execute(create table edwwrkuser.staging_customer as
select * from edwwrkuser.Cusomter table
where create_dt between &start_dt and &enddate) with data primary index(cust_id)) by teradata;
execute(commit work) by Teradata;
disconnect from teradata;
quit;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 230 views
  • 0 likes
  • 7 in conversation