My code easily handles the different situations. For sum, run the code below.
data want(drop=_bsl);
do _N_=1 by 1 until (last.sub | last.name);
set new;
by sub name notsorted;
_bsl = sum(bsl, _bsl);
end;
do _N_=1 to _N_;
set new;
bsl = _bsl;
output;
end;
run;
So what does your desired result look like from this data?
Here is an approach, using a hash:
data want;
if _n_=1 then do;
declare hash h (dataset:'new (where=(bsl ne .))');
h.definekey('sub','name');
h.definedata('bsl');
h.definedone();
end;
set new;
if h.find()=0 then output;
run;
Another approach could be:
proc sort data=new out=new_sorted;
by sub name descending bsl;
run;
data want;
set new_sorted;
retain bsl_new;
if bsl ne . then bsl_new = bsl;
drop bsl;
rename bsl_new = bsl;
run;
Data new;
Input sub name $ bsl;
datalines;
101 BT .
101 BT 36.8
101 BT .
101 BT .
101 BT .
101 RR .
101 RR 16
101 RR .
101 He 170
102 BT .
102 BT 33.4
102 BT .
102 BT .
102 BT .
102 He 160
102 RR .
102 RR 16
102 RR .
;
data want(drop=_bsl);
do _N_=1 by 1 until (last.sub | last.name);
set new;
by sub name notsorted;
_bsl = max(bsl, _bsl);
end;
do _N_=1 to _N_;
set new;
bsl = _bsl;
output;
end;
run;
What if you have more than one non-missing value per group, which one should take precedence?
My code easily handles the different situations. For sum, run the code below.
data want(drop=_bsl);
do _N_=1 by 1 until (last.sub | last.name);
set new;
by sub name notsorted;
_bsl = sum(bsl, _bsl);
end;
do _N_=1 to _N_;
set new;
bsl = _bsl;
output;
end;
run;
@sasuser123123 glad you found your answer 🙂
1) My code returns a non-mising value fir the 'He' record when you run the code below.
2) Shortly put, the code is structured as follows. I read the data twice, one By-Group at the time using a Double DoW Loop. For each By-Group, I use the Sum Function to calculate the sum of the bsl variable. This will give me the sum of bsl for the entire group. Next, I read the same By-Group in the second DoW loop and assign the sum of bsl (_bsl) to each value of bsl and then output.
Hope it makes more sense now. Otherwise, don't hesitate to ask 🙂
Data new;
Input sub name $ bsl;
datalines;
101 BT .
101 BT 36.8
101 BT .
101 BT .
101 BT .
101 RR .
101 RR 16
101 RR .
101 He 170
102 BT .
102 BT 33.4
102 BT .
102 BT .
102 BT .
102 He 160
102 RR .
102 RR 16
102 RR .
;
data want(drop=_bsl);
do _N_=1 by 1 until (last.sub | last.name);
set new;
by sub name notsorted;
_bsl = max(bsl, _bsl);
end;
do _N_=1 to _N_;
set new;
bsl = _bsl;
output;
end;
run;
Result:
sub name bsl 101 BT 36.8 101 BT 36.8 101 BT 36.8 101 BT 36.8 101 BT 36.8 101 RR 16.0 101 RR 16.0 101 RR 16.0 101 He 170.0 102 BT 33.4 102 BT 33.4 102 BT 33.4 102 BT 33.4 102 BT 33.4 102 He 160.0 102 RR 16.0 102 RR 16.0 102 RR 16.0
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.