Last Observation using multiple variables

[Stretlow]

Hi there!

i can see similar questions have been asked on the board before however i cant seem to figure out an answer to my problem so apologies if it has been covered and i've missed it.

I have a dataset with multiple variables 3 of which are numeric unique identifiers and one is a date.

A sample of what it looks like could be this

AccountNumber1  PNumber SNumber Date

112335                  2233         124         01/01/2017

112335                  2233         124         06/01/2017

112335                  1111          126         06/01/2017

112335                  1111          126         07/01/2017

112335                  2233         124         07/01/2017

112257                  2234         124         01/01/2017

etc.

What im looking to find the last observation by checking the other 3 numeric variables.

So from the above sample the output would be 

112335                  2233         124         07/01/2017

112335                  1111          126         07/01/2017

112257                  2234         124         01/01/2017

Im sure the data needs to be sorted by the three variables first but im a little stuck from there on.

Any help greatly appeicated.

Stret

[user24feb · Accepted solution]

Try:

Data A;
  Infile Datalines Missover;
  Input AccountNumber1  PNumber SNumber Date:DDMMYY10.;
  Format Date Date9.;
  Datalines;
112335                  2233         124         01/01/2017
112335                  2233         124         06/01/2017
112335                  1111          126         06/01/2017
112335                  1111          126         07/01/2017
112335                  2233         124         07/01/2017
112257                  2234         124         01/01/2017
;

Proc SQL;
  Create Table Want As
    Select AccountNumber1,PNumber,SNumber,Max(Date) As Date Format=Date9.
	From A
	Group By AccountNumber1,PNumber,SNumber;
Quit;

[BrunoMueller]

Hi You could use Proc SQL and find the maximum value for the date within a group, like so:

data have;
  infile cards;
  input
    AccountNumber1  PNumber SNumber Date : mmddyy.
  ;
  format date date9.;
  cards;
112335 2233 124 01/01/2017
112335 2233 124 06/01/2017
112335 1111 126 06/01/2017
112335 1111 126 07/01/2017
112335 2233 124 07/01/2017
112257 2234 124 01/01/2017
;

proc sql;
  select
    AccountNumber1
    , PNumber
    , SNumber
    , max(Date) as lastDate format=date9.
    , count(*) as nRows
  from
    have
  group by
    AccountNumber1
    , PNumber
    , SNumber
  ;
quit;

Bruno

[RW9]

proc sort data=have;
  by accountnumber1 pnumber snumber date;
run;
data want;
  set have;
  by accountnumber1 pnumber snumber;
  if last.accountnumber1 then output;
run;

[ballardw]

Solution chosen could well depend on whether there are other variables you need along with that specific record.

If not here's yet another solution:

proc summary data=have nway;

    class AccountNumber1  PNumber SNumber;

    var date;

    output out=want (drop=_:) max =;

run; 

[Tom]

If you want all unique combinations of the three variables that occur in the data then you need to just test the LAST. variable for the last id variable.

proc sort data=have;
  by accountnumber1 pnumber snumber date;
run;
data want;
  set have;
  by accountnumber1 pnumber snumber;
  if last.snumber ;
run;

[novinosrin]

data _null_;
if (_n_ = 1) then do;
if 0 then set have;
declare hash myhash(dataset: "have", duplicate: "r", ordered:'a');
 rc = myhash.definekey('AccountNumber1','PNumber','SNumber');
 rc = myhash.definedata('AccountNumber1','PNumber','SNumber','Date');
myhash.definedone();
end;
rc = myhash.output(dataset:"want");
run;

[Stretlow]

Thank you for yourswift help with this.

Much appreciated