topic Re: Find difference between two teradata dates in sas in SAS Programming

SAS date and teradata dates

suresh123 — Thu, 03 May 2018 18:14:28 GMT

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.

Re: SAS date and teradata dates

SuryaKiran — Thu, 03 May 2018 19:38:33 GMT

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.

Re: SAS date and teradata dates

Reeza — Thu, 03 May 2018 19:47:35 GMT

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

Re: SAS date and teradata dates

kiranv_ — Fri, 04 May 2018 03:17:02 GMT

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;

Re: SAS date and teradata dates

suresh123 — Fri, 04 May 2018 06:41:17 GMT

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';

Find difference between two teradata dates in sas

suresh123 — Fri, 04 May 2018 14:34:59 GMT

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.

Re: Find difference between two teradata dates in sas

novinosrin — Fri, 04 May 2018 14:40:44 GMT

HI @suresh123 Good morning, I have feeling you probably haven't read what @Reeza and @Kurt_Bremser 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.

Re: Find difference between two teradata dates in sas

MarkWik — Fri, 04 May 2018 14:44:50 GMT

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

Re: Find difference between two teradata dates in sas

suresh123 — Fri, 04 May 2018 14:52:05 GMT

Thanks novinosrin. I will look into it.

Re: Find difference between two teradata dates in sas

RW9 — Fri, 04 May 2018 14:52:44 GMT

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?

Re: Find difference between two teradata dates in sas

suresh123 — Fri, 04 May 2018 14:53:19 GMT

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

Re: SAS date and teradata dates

Reeza — Fri, 04 May 2018 15:03:21 GMT

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.

Re: Find difference between two teradata dates in sas

Reeza — Fri, 04 May 2018 15:08:11 GMT

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