BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Stretlow
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
user24feb
Barite | Level 11

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

7 REPLIES 7
user24feb
Barite | Level 11

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
SAS Super FREQ

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
Diamond | Level 26 RW9
Diamond | Level 26
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
Super User

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
Super User Tom
Super User

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
Tourmaline | Level 20

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
Obsidian | Level 7

Thank you for yourswift help with this.

 

Much appreciated

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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