Solved
Contributor
Posts: 66

# Last Observation using multiple variables

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

Accepted Solutions
Solution
‎04-19-2017 03:54 AM
Super Contributor
Posts: 355

## Re: Last Observation using multiple variables

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

All Replies
Solution
‎04-19-2017 03:54 AM
Super Contributor
Posts: 355

## Re: Last Observation using multiple variables

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;``````
SAS Super FREQ
Posts: 825

## Re: Last Observation using multiple variables

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

Super User
Posts: 9,599

## Re: Last Observation using multiple variables

```proc sort data=have;
by accountnumber1 pnumber snumber date;
run;
data want;
set have;
by accountnumber1 pnumber snumber;
if last.accountnumber1 then output;
run;
```
Super User
Posts: 13,583

## Re: Last Observation using multiple variables

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;

Super User
Posts: 8,127

## Re: Last Observation using multiple variables

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;``````
PROC Star
Posts: 1,836

## Re: Last Observation using multiple variables

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;

Contributor
Posts: 66

## Re: Last Observation using multiple variables

Thank you for yourswift help with this.

Much appreciated

☑ This topic is solved.