Proc Sql (or data step): assign records into groups based on multiple conditions

[N8]

Hi everyone, 

I'm having difficult building a solution to a problem I'm working on where I assign a value to a recipient based on multiple conditions. This can be done in a data step or SQL. 

Scenario: I'm working with a dataset that Has an ID variable, a claimtype (e.g., emergency, outpatient), the type of provider the person saw - and the important variable - was the provider they saw the one that was assigned to them. In all instances, the recipients is going to have an ED encounter, and I need to keep this variable. What i want to do is group recipients into three groups (A) always saw assigned provider, (B) sometimes saw assigned provider, and (C) never saw assigned provider. Ultimately these new variables will be my grouping variables to run some frequency statistics on (with dependent variable being ED visit) .

I've created a Want and Have dataset below. Any help on this is appreciated. I'm not too worried about the blanks that I have in the Want table as I plan to just populate the empty spaces using a Retain function, but if you know of an easier way to flag a recipient, by ID, into one of the 3 groups then that's also great!

thank you for your help, Nate

data Have;
input ID $ Claim $ provtype $ assigned &;
datalines ;
1 ED EM .
1 OP SPEC .
1 OP SPEC .
1 OP MD 1
1 OP MD 0
2 ED EM .
2 OP MD 1
2 OP MD 1
3 ED EM .
3 OP SPEC .
3 OP MD 1
3 OP MD 1
3 OP MD 1
3 OP MD 1
3 OP MD 1
4 ED EM .
4 OP MD 0
4 OP SPEC .
4 OP MD 0
;
;
run ;
data want;
input ID $ Claim $ provtype $ assigned & both $ always $ never $;
datalines ;
1 ED EM . . . .
1 OP SPEC . . . .
1 OP SPEC . . . .
1 OP MD 1 1 . .
1 OP MD 0 1 . .
2 ED EM . . . .
2 OP MD 1 . 1 .
2 OP MD 1 . 1 .
3 ED EM . . . .
3 OP SPEC . . . .
3 OP MD 1 1 . .
3 OP MD 1 1 . .
3 OP MD 1 1 . .
3 OP MD 1 1 . .
3 OP MD 1 1 . .
4 ED EM . . . .
4 OP MD 0 . . 1
4 OP SPEC . . . .
4 OP MD 0 . . 1

;

[Reeza]

Is seeing the assigned provider true when assigned=provtype? Or how is that defined?

[N8]

Hi Reeza - Sorry about that - prototype 1 = went to assigned provider, prototype = 0 did not go to assigned provider. The values could also be character. They are flagged as 1/0 in the database. In this example, prototype = SPEC is unimportant. It's prototype = MD that is important as this is the provider type that is triggering the flag.

[Reeza · Accepted solution]

proc sql;
create table want as
select *, 
case when max(assigned)=min(assigned) = 1 then 'Always see assigned provider'
when max(assigned) = min(assigned) = 0 then 'Never saw assigned provider'
when max(assigned) ne min(assigned) then 'Sometimes see assigned provider' 
else 'CHECKME' end as group_status
group by ID;
quit;

---

@N8 wrote:
Hi Reeza - Sorry about that - prototype 1 = went to assigned provider, prototype = 0 did not go to assigned provider. The values could also be character. They are flagged as 1/0 in the database. In this example, prototype = SPEC is unimportant. It's prototype = MD that is important as this is the provider type that is triggering the flag.

---

[N8]

perfect - i can work with this to get exactly what I am trying to figure out. Thank you!

[ballardw]

You show for your have data:

input ID $ Claim $ provtype $ assigned &;
datalines ;
1 ED EM .
1 OP SPEC .
1 OP SPEC .
1 OP MD 1
1 OP MD 0
2 ED EM .

which shows a large number of Assigned values as missing. Why? I would think, from you problem description it should be there everytime.

I really wouldn't add three variables one could do it just fine (with a format).

If you average the assigned variable per Id you get the percentage of times assigned is 1. So a value of 0 would mean "never", 1 "always" and anything else "sometimes". Which groups could be defined with a format

Proc format;
value assigned
0 = 'Never'
0 <-<1 = 'Sometimes'
1 ='Always'
;

[N8]

Hi Ballardw - thanks for your note - the '.' values in this example set are assigned to all prov=SPEC type as they represent referrals or other providers that aren't assigned. In the dataset all of the providers (by type) are listed in the same column. It is only the provtype=MD that triggers a flag of 1 = this was the assigned provider or 0 = this was not their assigned provider. Either way, I need to keep all of the records in the dataset, so the missing values need to stay, though i admit i could just run a proc sql create table based on the grouper and then prob join back based on b.id = a.id and b.provtype = a.provtype.

[ballardw]

Yes, attach the value back to the records that need it.

I don't generally add summary type values back to every record of a data set as there are just too many ways for such to get misunderstood or forget what they are there for if the set isn't used for awhile, or I have a "senior moment" reusing the data for another purpose.