How to retain the values

[sasuser123123]

Hello..

I've data..

Data new;
Input sub name $ bsl
Cards;
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 .


So I Need to retain missing values with the existing value for each name(BT,RR).How to do that.could one
anyone please help me for this query..

Thank you

Regards..

[PeterClemmensen]

So what does your desired result look like from this data?

[sasuser123123]

Thank you for quick response..

My output should be like..

101 BT 36.8
101 BT 36.8
101 BT 36.8
101 BT 36.8
101 BT 36.8
101 RR 16
101 RR 16
101 RR 16
101 He 170
102 BT 33.4
102 BT 33.4
102 BT 33.4
102 BT 33.4
102 BT 33.4
102 He 160
102 RR 16
102 RR 16
102 RR 16

Thank you!!

[ed_sas_member]

Hi @sasuser123123 

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;

[ed_sas_member]

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;

[PeterClemmensen]

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;

[Kurt_Bremser]

What if you have more than one non-missing value per group, which one should take precedence?

[sasuser123123]

It depends on condition that they would give...

Like average or sum of that values

[PeterClemmensen · Accepted solution]

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]

Hello @PeterClemmensen I just used your code and got desired values wherever multiple observations per name , but got missing value for 'He'. record

And also why did we used max or sum function

I didn't get your code.. could you please explain it in short .


Thank you

[PeterClemmensen]

@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 

[sasuser123123]

Thank you so much for your assistance