MAX dates issue

Reply
Frequent Contributor
Posts: 80

MAX dates issue


Hi

I am using SAS Enterprise Guide 4.3 with Windows XP Pro.

I have created 6 tables with various fields including a 'Month' field, Account number, Balance & a 'DateTime' transaction value.

I had to create 6 as running one query for this was too much for the Server.

Now I have Appended the 6 tables together so I can query it & find the overall MAX dateTime value for an account number.

I then created a new table with only the Account Number, Balance & 'DateTime' field.

I am trying to get the MAX DateTime value so I can find the latest balance but I find instead, I get the latest balance for each month even though I'm not using the 'Month' field in the query.

Is there something about SAS EG taht works this way?

If so or anyone can help me how do i egt the latest dateTime value from this new appended table??

Thanks 

PROC Star
Posts: 7,363

Re: MAX dates issue

I'm not well versed in EG, but do know that you can run proc sql code from it.  As such, why not just create and run the following program:

proc sql;

select distinct max(yourfieldname)

   from yourfilename

;

quit;

Frequent Contributor
Posts: 80

Re: MAX dates issue

Thanks Arthur

I've tried it but it still reports the total from each table.

I'm writing it as yours but with the Distinct inside the MAX(DateTime) brackets, like this

Select A/c, Balance, Max(Distinct ''DateTime) - because if I write it like yours, It says it doesn't recognise the fieldname 'Distinct'.

Any other thoughts?

PROC Star
Posts: 7,363

Re: MAX dates issue

Actually, this distinct isn't even needed as you can see from:

proc sql;

  select max(age)

    from sashelp.class

  ;

quit;

However, I'm confused that you say it is reporting max(date) from each table.  I thought you were doing this from just one table.

PROC Star
Posts: 7,363

Re: MAX dates issue

further, when I look at what you wrote, it appears like you are taking values from other fields.  Are you using a group by statement?  That would explain your undesired results.

Post your entire sql statement or we (those on the forum) really can't respond with good advice.

Frequent Contributor
Posts: 80

Re: MAX dates issue

So how do I use an aggregate function like MAX without using Group By?

Frequent Contributor
Posts: 80

Re: MAX dates issue

I think you've got it Athur.

It must be the group by that's causing it.

The reason for you thinking it as more than one table is becuase it was 6 tables then appended into 1 table, Cheers

Frequent Contributor
Posts: 80

Re: MAX dates issue

OK, I've worked out I can leave out the 'Group By' statement within the Procedure, so that's handy but now I'm getting the Max (DateTime) of the entire table rather than the Max(DateTime) for the row?

%_eg_conditional_dropds(WORK.QUERY_FOR_APPEND_TABLE);

PROC SQL;

   CREATE TABLE WORK.QUERY_FOR_APPEND_TABLE AS

   SELECT DISTINCT t1.A_MSISDN, MAX(t1.DATESTARTOFCHARGING) FORMAT=DATETIME20.

   AS MAX_of_DATESTARTOFCHARGING,

          t1.'BALANCE_$'n

      FROM WORK.APPEND_TABLE_0000 t1

      WHERE t1.A_MSISDN IN

           (

           '0400XXXXX',

           '0400XXXXX',

           '0400XXXXXX'

           )

     /*PUTTING THESE 2 ROWS BELOW IN GIVE ME ONLY THE ONE RESULT WHICH HAS THE

     MAX(DATETIME) FOR THE WHOLE TABLE - LEAVE IT OUT & I STILL GET MULTIPLE DATETIMES FOR THE A_MSISDN * /

           HAVING t1.DATESTARTOFCHARGING = MAX(t1.DATESTARTOFCHARGING)

           AND t1.A_MSISDN = t1.A_MSISDN

      /*GROUP BY t1.A_MSISDN, t1.'BALANCE_$'n*/

          

;

QUIT;

PROC Star
Posts: 7,363

Re: MAX dates issue

Still not clear what you are trying to accomplish.  Possibly, you want to group only by t1.a_msisdn

Frequent Contributor
Posts: 80

Re: MAX dates issue

Hi Arthur

I'm trying to get the latest DateTime value from this appended table & Balance for each account number.

Instead, I'm getting the latest dateTime value for January, then February, etc etc when I want the latest date overall for that account number.

The latest dateTime might be in January or May or whichever month, but I only want that latest one for that account number.

Hope this clarifies the question, Cheers

PROC Star
Posts: 7,363

Re: MAX dates issue

maybe I wasn't sufficiently clear.  You had one unnecessary condition in your call (I think).  Wouldn't you get what you want by substituting the following for what you have?:

           HAVING t1.DATESTARTOFCHARGING = MAX(t1.DATESTARTOFCHARGING)

             GROUP BY t1.A_MSISDN

Frequent Contributor
Posts: 80

Re: MAX dates issue

Hi Arthur

I tried; 
GROUP BY t1.A_MSISDN

HAVING t1.DATESTARTOFCHARGING = MAX(t1.DATESTARTOFCHARGING) 

and it seems to work OK.

I got drafted onto something urgent so I haven't fully tested it yet, but will come back to you, Cheers

Established User
Posts: 1

Re: MAX dates issue

select datepart(max(TABLE.DATEFIELD)) format=date9. 
from...

seems to work.

Contributor
Posts: 29

Re: MAX dates issue

Hello Guru's,

I would like to pick the max date of a field using SAS Code, can any one please advise?

Example:

student  date

1          10apr2012

1          10apr2011

1          10apr2010

I want to have 10apr2012 selected.

Thanks in advance.


PROC Star
Posts: 7,363

Re: MAX dates issue

Rish,

In the future, when you have a question start a new discussion.  Otherwise, it is likely never to be noticed.

That said, you could use:

data have;

  informat date date9.;

  format date date9.;

  input student  date;

cards;

1          10apr2012

1          10apr2011

1          10apr2010

;

proc sql;

  select max(date) format=date9.

    from have

  ;

quit;

Ask a Question
Discussion stats
  • 14 replies
  • 7448 views
  • 3 likes
  • 4 in conversation