Find the last codes

[BaalaRaaji]

Hi Guys, 

I have got below sample data set  which has codes for latest year(2002).

data inventory_LATEST;
input PartNumber $ Description $ InStock @17
ReceivedDate date9. @27 code $;
format ReceivedDate date9.;
datalines;
K89R seal 34 27jul2002 H1
M4J7 sander 98 20jun2002 H2
LK43 filter 121 19may2002 F1
MN21 brace 43 10aug2002 F2
BC85 clamp 80 16aug2002 G
NCF3 valve 198 20mar2002 G1
KJ66 cutter 6 18jun2002 G2
UYN7 rod 211 09sep2002 G3
JD03 switch 383 09jan2002 H1
BV1E timer 26 03aug2002 H2
;
run;

And the other dataset has 3 years of smilar find with different codes..

data inventoryhist;
input PartNumber $ Description $ InStock @17
ReceivedDate date9. @27 code $;
format ReceivedDate date9.;
datalines;
K89R seal 34 27jul1999 T
K89R seal 34 27jul1998 H1
K89R seal 34 27jul1997 G
M4J7 sander 98 20jun1999 L
M4J7 sander 98 20jun1998 H2
M4J7 sander 98 20jun1997 H2
LK43 filter 121 19may2000 M
LK43 filter 121 19may1998 F1
LK43 filter 121 19may1997 T
MN21 brace 43 10aug2000 F2
MN21 brace 43 10aug1998 F2
MN21 brace 43 10aug1997 F2
BC85 clamp 80 16aug2000 K
BC85 clamp 80 16aug1998 G
BC85 clamp 80 16aug1997 L
NCF3 valve 198 20mar2000 G1
NCF3 valve 198 20mar1998 G1
NCF3 valve 198 20mar1997 K
KJ66 cutter 6 18jun2000 J
UYN7 rod 211 09sep1998 G3
UYN7 rod 211 09sep1999 H
UYN7 rod 211 09sep2000 H
JD03 switch 383 09jan2001 L
JD03 switch 383 09jan1998 H1
JD03 switch 383 09jan1999 H1
BV1E timer 26 03aug2001 M
BV1E timer 26 03aug1998 H2
BV1E timer 26 03aug1999 H1
;
run;

i want to write a code that pick the last codes(which are different to latest(2002).

output shouls be like below,,

PartNumber Description InStock ReceivedDate code prevcode
K89R seal 34 27jul2002 H1 T
M4J7 sander 98 20jun2002 H2 L
LK43 filter 121 19may2002 F1 M
MN21 brace 43 10aug2002 F2 F2
BC85 clamp 80 16aug2002 G K
NCF3 valve 198 20mar2002 G1 G1
KJ66 cutter 6 18jun2002 G2 J
UYN7 rod 211 09sep2002 G3 H
JD03 switch 383 09jan2002 H1 L
BV1E timer 26 03aug2002 H2 M

Any help much appreciated...i have explained one senario for one record.

(first dataset)

K89R seal   34  27jul2002 H1 

(second dataset)

K89R seal 34 27jul1999 T
K89R seal 34 27jul1998 H1
K89R seal 34 27jul1997 G

Final dataset(latest to pick is for this record after 2002-1999) so on

K89R seal   34  27jul2002 H1  T

please let me know if you have questions.thanks

[Kurt_Bremser]

Try this:

data inventory_LATEST;
input
  PartNumber $
  Description $
  InStock
  ReceivedDate :date9.
  code $
;
format ReceivedDate date9.;
seq = _n_;
datalines;
K89R seal 34 27jul2002 H1
M4J7 sander 98 20jun2002 H2
LK43 filter 121 19may2002 F1
MN21 brace 43 10aug2002 F2
BC85 clamp 80 16aug2002 G
NCF3 valve 198 20mar2002 G1
KJ66 cutter 6 18jun2002 G2
UYN7 rod 211 09sep2002 G3
JD03 switch 383 09jan2002 H1
BV1E timer 26 03aug2002 H2
;
run;

data inventoryhist;
input
  PartNumber $
  Description $
  InStock
  ReceivedDate :date9.
  code $
;
format ReceivedDate date9.;
datalines;
K89R seal 34 27jul1999 T
K89R seal 34 27jul1998 H1
K89R seal 34 27jul1997 G
M4J7 sander 98 20jun1999 L
M4J7 sander 98 20jun1998 H2
M4J7 sander 98 20jun1997 H2
LK43 filter 121 19may2000 M
LK43 filter 121 19may1998 F1
LK43 filter 121 19may1997 T
MN21 brace 43 10aug2000 F2
MN21 brace 43 10aug1998 F2
MN21 brace 43 10aug1997 F2
BC85 clamp 80 16aug2000 K
BC85 clamp 80 16aug1998 G
BC85 clamp 80 16aug1997 L
NCF3 valve 198 20mar2000 G1
NCF3 valve 198 20mar1998 G1
NCF3 valve 198 20mar1997 K
KJ66 cutter 6 18jun2000 J
UYN7 rod 211 09sep1998 G3
UYN7 rod 211 09sep1999 H
UYN7 rod 211 09sep2000 H
JD03 switch 383 09jan2001 L
JD03 switch 383 09jan1998 H1
JD03 switch 383 09jan1999 H1
BV1E timer 26 03aug2001 M
BV1E timer 26 03aug1998 H2
BV1E timer 26 03aug1999 H1
;
run;

proc sort data=inventory_latest;
by partnumber;
run;


proc sort data=inventoryhist;
by partnumber descending receiveddate;
run;

data want;
merge
  inventory_latest (in=a)
  inventoryhist (
    in=b
    keep=partnumber code
    rename=(code=prevcode)
  )
;
by partnumber;
if a;
if first.partnumber;
run;

proc sort data=want;
by seq;
run;

Note that I had to modify the input statements to correctly work.

[BaalaRaaji]

KurtBremser you are Genius....I need more help on this query...

The first part code has 1700records with codes as F,G,H...as on 31MAR2018

I have to find for these records in historic table which has data since 2011..that has codes other than F,G,H..comparing our previous file...
It has some billion records...(historic one)

Can you advice how to write a code..?

Appreciate your help...thank you...

[Kurt_Bremser]

If the structure is as you showed, the code will scale, it's just a matter of time.

If it's only 1700 partnumbers, those will easily fit into memory, so I'd start with

data _null_;
set inventory_latest (keep=partnumber) end=eof;
length numbers $32767;
retain numbers;
numbers = catx(',',numbers,quote(strip(partnumber)));
if eof then call symputx('partnumbers',numbers);
run;

proc sort
  data=inventoryhist (
    where=(partnumber in (&partnumbers))
  )
  out=temphist
;
by partnumber descending receiveddate;
run;

to reduce the history table to the necessary minimum.

[ChrisNZ]

@Kurt_Bremser I haven't seen that method for value-concatenation since   SQL into   appeared. Old habits die hard! 🙂

I stick to  into  not only for the compact code, but also because it allows 64 kB worth of values rather than 32kB; unless there are so many values that several lists are necessary.     

[BaalaRaaji]

Thank you @Kurt_Bremser...

I learned new techniques from you...Never used in proc sort..always used as SQL into clause..

Am still working on my query as i have to use two variables in join and sort it...i hope i will get it down soon...thanks for your help..

Cheers!