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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

12 REPLIES 12
SuryaKiran
Meteorite | Level 14

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
Reeza
Super User

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

suresh123
Calcite | Level 5

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

suresh123
Calcite | Level 5

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.
novinosrin
Tourmaline | Level 20

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.

suresh123
Calcite | Level 5
Thanks novinosrin. I will look into it.
MarkWik
Quartz | Level 8

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

suresh123
Calcite | Level 5
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
Reeza
Super User

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

Reeza
Super User

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.

 

 

kiranv_
Rhodochrosite | Level 12

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;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 12 replies
  • 10449 views
  • 0 likes
  • 7 in conversation