Getting data from previous rows to categorise with a Hierarchy

[FrankvP]

Good morning from Holland,

This will be the first time asking a question concerning sas. I've only been using SAS EG for a short period of time, getting pretty used to the drag and drop part of it. But I need to get in to data steps more and more. I've been struggling with this problem for quite a bit.

I'll try to explain the question first. Hope I picked a decent title and put the topic in the right area. (Hardest thing of asking a question is coming up with a good title )

Question:

In my dataset I have Clients (Nmr) that have different "problems" that occur in our system handling these clients (conflicting data or business rules) that need intensive handlabor to fix. What i want to do is find out what kind of problems occur and where they lead.

So i've categorised the sorts of problems with a number (category) 1 beeing the biggest problem and a higher number indicates smaller problems (the hierarchy). The records are sorted on the date the problem occured.

What i want to do is analyse the clients and see what route the problems take (taking a hierarchy into acocunt) so when a client has a problem category 10 on date 1 i want it to show 10.. on date 2 the client has a problem category 6 i want it to show 6, but when on date 3 the client has another problem category 10 i want it to keep showing 6 until a higher ranked problem occurs.

I've been trying some stuf with LAGs with some IF's and I have been trying to do some loops (most of them infinity :smileysilly:) which did not gave me the result I wanted. In the data set below i have 4 collumns. I can't solve it with a few consecutive LAG's because that could mean looking back 100 rows which is quite common.

Collumn 1 (Nmr)                     = The client number

Collumn 2 (Category)             = The category number 1 beeing highest ranked

Collumn 3 (Date)                    = Date in DDMMYY

Collumn 4 (WantedResults)   = The results i would like for these 2 test records

Data Set:

data OriginOfPorblems;

input Nmr $ Category $ Date : anydtdte9. WantedResults;

format Date ddmmyy10.;

return;

datalines;

1 14 1-2-2014 14

1 14 9-2-2014 14

1 7 4-3-2014 7

1 3 4-4-2014 3

1 3 22-5-2014 3

1 14 20-6-2014 3

1 13 12-12-2014 3

2 13 14-3-2014 3

2 13 14-3-2014 13

2 13 14-3-2014 13

2 13 14-3-2014 13

2 13 17-3-2014 13

2 13 17-3-2014 13

2 10 24-9-2014 10

2 13 5-11-2014 10

2 15 5-11-2014 10

2 13 11-11-2014 10

2 16 24-11-2014 10

2 7 2-12-2014 7

;

run;

Bit of a long Post but I hope i've been able to adress my issue in an understandable fassion. If there are any pointers or if I did something terribly wrong asking the question, feedback is welcome

Thanks a lot for the responses,

Cheer,

Frank

[gergely_batho · Accepted solution]

This is a kind of "cumulative minimum" problem (for each group):

data want;

  set OriginOfPorblems;

  by Nmr;

  retain HighestCategory;

  if first.Nmr then do;

  HighestCategory=.;

  end;

  HighestCategory=min(input(Category,3.), HighestCategory);  /*Converting to number, and taking the cmulative minimum*/

run;

[FrankvP]

Thanks so much! Exactly what I was looking for!

Thanks!