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
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;
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;
Thanks so much! Exactly what I was looking for!
Thanks!
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.
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.