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
;
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.
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.
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' ;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.