topic Re: max date in create table in sql in SAS Programming

max date in create table in sql

sarahzhou — Wed, 23 Feb 2022 04:15:58 GMT

Hi Instructors,

I created table as below:

create table WORK.RelationTable as select * from connection to ServerName
(select distinct A.*
           ,INDV_KEY
           ,TO_KEY
           ,RLTN_TX
           ,RLTN_STRT_DT
           ,max(RLTN_END_DT) as RLTN_END_DT from &TableSourceOne. A
INNER join ##TableSourceTwo B
on (A.INDV_KEY=B.TO_INDV_KEY)
);

the error occurs at max(RLTN_END_DT) as RLTN_END_DT, when I change back to RLTN_END_DT, then it runs ok.

I tried add a filter where RLTN_END_DT=max(RLTN_END_DT), it doesn't work

What shoud I do?

Thank you!

RLTN_END_DT: relation end datetime

Re: max date in create table in sql

Kurt_Bremser — Wed, 23 Feb 2022 06:08:20 GMT

Please post the complete log from that step into a window opened with this button:
Since this happens in explicit pass-through, you should consult sources that deal with that specific DBMS

Re: max date in create table in sql

ChrisNZ — Wed, 23 Feb 2022 06:09:43 GMT

1. Is max a valid function name in your database?

2. Do you really want to use max() and have no group by clause?

Re: max date in create table in sql

sarahzhou — Wed, 23 Feb 2022 07:48:30 GMT

I added group by clause

create table WORK.RELATION as select * from connection to SQLSERVER
		    (select distinct A.*, INDV_KEY, TO_INDV_KEY, RLTN_TX, RLTN_STRT_DT, MAX(RLTN_END_DT) as RLTN_END_DT from &TABLENAMEONE. A
				INNER join ##TABLEDATATWO B
					on (A.INDV_KEY=B.TO_INDV_KEY)				
			)GROUP BY INDV_KEY;

ERROR: CLI describe error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Column 'TABLENAMEONE.INDV_KEY' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. :
[SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The batch could not be analyzed because of compile errors.

what is wrong with my "MAX(RLTN_END_DT)".

Please help. Thank you!

Re: max date in create table in sql

ChrisNZ — Wed, 23 Feb 2022 08:11:43 GMT

The message couldn't be clearer:

Column 'TABLENAMEONE.INDV_KEY' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

> what is wrong with my "MAX(RLTN_END_DT)".

Nothing. As the message mentions, the issue is with INDV_KEY

What result do you expect from this query? What total time do you expect ?

Also adding group by outside of the parentheses makes no sense.

Re: max date in create table in sql

sarahzhou — Wed, 23 Feb 2022 08:22:24 GMT

In previous code which run correct and get the table below

INDV_KEY	TO_INDV_KEY	RLTN_TX	RLTN_STRT_DT	RLTN_END_DT
1F6A57115A	1F6A57115B	member	14-Feb-18	30-Apr-18
1F6A57115A	1F6A57115B	member	24-Feb-18	.

I only want the first row to be returned.

Thank you

Re: max date in create table in sql

Kurt_Bremser — Wed, 23 Feb 2022 08:40:09 GMT

First of all, you must tell SQL from where each individual column has to be taken, so all of your column names should either have an A. or B. as prefix. Even if the procedure does not complain (because a certain column is contained in only one table), working without prefixes is sloppy programming.

Finding a single record in a group is done like this in SQL:

select a.*
from table a
group by a.indv_key
having rltn_end_dt = max(a.rltn_end_dt)

Re: max date in create table in sql

sarahzhou — Wed, 23 Feb 2022 08:52:25 GMT

Thank you! The code was fixed. 🙂
Thank you very much!