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

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 Smiley Happy)

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 Smiley Happy

Thanks a lot for the responses,

Cheer,

Frank

1 ACCEPTED SOLUTION

Accepted Solutions
gergely_batho
SAS Employee

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;

View solution in original post

2 REPLIES 2
gergely_batho
SAS Employee

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
Calcite | Level 5

Thanks so much! Exactly what I was looking for!

Thanks!

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 2 replies
  • 711 views
  • 0 likes
  • 2 in conversation