Assign value based on status

[TheNovice]

Hi there,

Trying to think the best way to do below.. I have multiple records for ID's and each ID has an end state every month ( F or B)

where ID's are always F, i want to tag them as Good. Where always B, I want to tag them as Bad and mixed when it is a mix

Would appreciate any and all inputs

ID	STATE	TYPE
1	F	GOOD
1	F	GOOD
1	F	GOOD
2	F	MIXED
2	F	MIXED
2	F	MIXED
2	B	MIXED
3	B	BAD
3	B	BAD
3	B	BAD
3	B	BAD
4	F	GOOD
4	F	GOOD
4	F	GOOD

[Reeza · Accepted solution]

SQL is nice here, aggregate functions work on character columns. So if the max is equal to the min then all values are the same. 

proc sql;
create table want as 
select *, case when max(state) = min(state) & max(state)= "F" then "Good"
                       when max(state) = min(state) & max(state)= "B" then "Bad"
                       else "MIXED" END as Type
from have
group by ID;
quit;

             

EDIT: Fixed an issue with the SQL 

[TheNovice]

Thank you so much Reeza and Ksharp, 

I combined your solutions because i needed to modify the outcome a bit. I did not know that the sum function could be used this way. 

case when max(state) = min(state) & max(state)= "F" then "Good"
when max(state) = min(state) & max(state)= "B" then "Bad"
when sum(state= 'F') > SUM(state= 'B') THEN 'G>B'
when sum(state= 'F') < SUM(state= 'B') THEN 'G<B'
when sum(state= 'F') = SUM(state= 'B') THEN 'G=B'
else '' end as Type

[Ksharp]

Are you both the same person ?

https://communities.sas.com/t5/SAS-Programming/Creating-one-variable-per-multiple-rows-per-ID/m-p/76...

[TheNovice]

Hi Ksharp, nope. just a coincidence. Thanks for the reply!

[TheNovice]

FYI<, thanks for directing me. your post showed me a different way to use the sum function and i modified the outcome a bit

[tarheel13]

proc sql;
	create table type as 
	
	select ID,count(ID) as count, sum(state='F') as F, sum(state='B') as B,
	case when count(ID) = sum(state='F') then 'GOOD'
	when count(ID)=sum(state='B') then 'BAD'
	else 'MIXED' end as type
		from import1
		group by ID;
		
	select i.*, type
		from import2  as i
		left join type as t
		on t.ID=i.ID;
		
quit;

[TheNovice]

Hi Irackley, thank you for the post but it didn't work. Could you explain the import1 and import2 datasets?

[tarheel13]

You didn’t post your data as datalines so I had to import it myself

[TheNovice]

Got it and I ran the query and it worked as well. thanks!