topic Re: One Value to Rule them all! Trouble using last value as value for all in group in SAS Programming

One Value to Rule them all! Trouble using last value as value for all in group

JMagenta — Fri, 10 Mar 2023 20:21:06 GMT

Hello,

I was having trouble using the last value in my groups as the value for all members in my groups. Basically, I want whatever the last value in the group is to be the value for all members in that group.

What I have:

data Have;
input ID Name $;
cards;
1 ''
1 Yes
2 '' 
2 No 
3 ''
3 '' 
3 Yes
4 No
5 '' 
5 Yes 
6 No 
8 Yes 
;
run;

Which looks like:

But What I WANT:

How can I achieve my goal? Please help.

Thank you in advance,

Re: One Value to Rule them all! Trouble using last value as value for all in group

russt_sas — Fri, 10 Mar 2023 20:56:34 GMT

Here is one way to accomplish your task:

data Have;
input ID Name $;
cards;
1 .
1 Yes
2 .
2 No
3 .
3 Yes
4 No
5 .
5 .
5 Yes
6 No
8 Yes
;
data new;
set have;
by id;
if last.id then output;
run;

data final;
merge have(in=a) new(rename=(name=name2));
by id;
if a;
run;

data final2;
set final;
retain name2;
by id;
if first.id then new=name2;
if name ne . then new=name;
keep id name2;
run;

proc print;
run;

Re: One Value to Rule them all! Trouble using last value as value for all in group

russt_sas — Fri, 10 Mar 2023 21:02:11 GMT

This is really all you need:

data Have;
input ID Name $;
cards;
1 .
1 Yes
2 .
2 No
3 .
3 Yes
4 No
5 .
5 .
5 Yes
6 No
8 Yes
;
data new;
set have;
by id;
if last.id then output;
run;

data final;
merge have(in=a) new(rename=(name=name2));
by id;
if a;
run;

Re: One Value to Rule them all! Trouble using last value as value for all in group

Kurt_Bremser — Fri, 10 Mar 2023 21:42:10 GMT

DOW loop:

data want;
do until (last.id);
  set have (rename=(name=_n));
  by id;
end;
do until (last.id);
  set have;
  by id;
  name = _n;
  output;
end;
drop _n;
run;

Re: One Value to Rule them all! Trouble using last value as value for all in group

JMagenta — Fri, 10 Mar 2023 21:45:05 GMT

This kind of worked, only I am now missing some data somehow. I also have duplicates.

Thank you,

Re: One Value to Rule them all! Trouble using last value as value for all in group

JMagenta — Fri, 10 Mar 2023 21:56:26 GMT

Thank you much!