topic Re: Last Observation using multiple variables in SAS Procedures

Last Observation using multiple variables

Stretlow — Tue, 18 Apr 2017 12:00:57 GMT

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

Re: Last Observation using multiple variables

user24feb — Tue, 18 Apr 2017 12:19:05 GMT

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;

Re: Last Observation using multiple variables

BrunoMueller — Tue, 18 Apr 2017 12:19:20 GMT

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

Re: Last Observation using multiple variables

RW9 — Tue, 18 Apr 2017 13:19:51 GMT

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

Re: Last Observation using multiple variables

ballardw — Tue, 18 Apr 2017 14:46:27 GMT

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;

Re: Last Observation using multiple variables

Tom — Tue, 18 Apr 2017 15:01:05 GMT

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;

Re: Last Observation using multiple variables

novinosrin — Wed, 19 Apr 2017 07:49:26 GMT

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;

Re: Last Observation using multiple variables

Stretlow — Wed, 19 Apr 2017 07:55:31 GMT

Thank you for yourswift help with this.

Much appreciated