Help using Base SAS procedures

Last Observation using multiple variables

Accepted Solution Solved
Reply
Contributor
Posts: 49
Accepted Solution

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: 336

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;

View solution in original post


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

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: 685

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
Super User
Posts: 7,413

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: 10,538

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=_Smiley Happy max =;

run; 

Super User
Super User
Posts: 6,502

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: 172

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: 49

Re: Last Observation using multiple variables

Thank you for yourswift help with this.

 

Much appreciated

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 157 views
  • 0 likes
  • 7 in conversation