topic Re: Assign value based on status in SAS Programming

Assign value based on status

TheNovice — Fri, 03 Sep 2021 19:20:47 GMT

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

Re: Assign value based on status

Reeza — Fri, 03 Sep 2021 19:58:54 GMT

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

Re: Assign value based on status

Ksharp — Sat, 04 Sep 2021 11:20:58 GMT

Are you both the same person ?

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

Re: Assign value based on status

tarheel13 — Sat, 04 Sep 2021 20:09:28 GMT

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;

Re: Assign value based on status

TheNovice — Sun, 05 Sep 2021 03:46:33 GMT

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

Re: Assign value based on status

TheNovice — Sun, 05 Sep 2021 04:04:01 GMT

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

Re: Assign value based on status

TheNovice — Sun, 05 Sep 2021 04:05:29 GMT

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

Re: Assign value based on status

TheNovice — Sun, 05 Sep 2021 04:06:32 GMT

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

Re: Assign value based on status

tarheel13 — Sun, 05 Sep 2021 06:36:01 GMT

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

Re: Assign value based on status

TheNovice — Wed, 08 Sep 2021 21:02:36 GMT

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