topic Re: coalesce once a condition is met in SAS Programming

coalesce once a condition is met

asinusdk — Wed, 28 Nov 2018 16:15:23 GMT

From the table below, if I want to make a variable that shows at least "1" in v1 as the same value,

year	id	v1
1	1	0
2	1	0
3	1	0
1	2	0
2	2	0
3	2	1
1	3	0
2	3	1
1	4	0
2	4	0
3	4	0

looks like this

year	id	v1	v2
1	1	0	0
2	1	0	0
3	1	0	0
1	2	0	1
2	2	0	1
3	2	1	1
1	3	0	1
2	3	1	1
1	4	0	0
2	4	0	0
3	4	0	0

How can I simply make a code?

Last time, I used first and last function, but it made too many datasets.

I think coalesce in proc sql can do something, but I don't know how to do it...

Could you please help me?

Re: coalesce once a condition is met

novinosrin — Wed, 28 Nov 2018 16:20:31 GMT

it's a straight forward proc sql

data have;
input year id	v1;
cards;
1	1	0
2	1	0
3	1	0
1	2	0
2	2	0
3	2	1
1	3	0
2	3	1
1	4	0
2	4	0
3	4	0
;

proc sql;
create table want as
select *,max(v1) as v2
from have
group by id
order by id,year;
quit;

Re: coalesce once a condition is met

asinusdk — Wed, 28 Nov 2018 16:24:40 GMT

Thank you very much. I'm learning from you a lot. Have a good day.

Re: coalesce once a condition is met

novinosrin — Wed, 28 Nov 2018 16:41:55 GMT

as usual some fun


data have;
input year id	v1;
cards;
1	1	0
2	1	0
3	1	0
1	2	0
2	2	0
3	2	1
1	3	0
2	3	1
1	4	0
2	4	0
3	4	0
;

data want;
merge have have(rename=(v1=_iorc_) where=(_iorc_=1) keep=id v1);
by id;
v2=_iorc_>.;
run;