topic Re: How to retain the values in SAS Programming

How to retain the values

sasuser123123 — Mon, 02 Mar 2020 09:26:14 GMT

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..

Re: How to retain the values

PeterClemmensen — Mon, 02 Mar 2020 09:27:08 GMT

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

Re: How to retain the values

sasuser123123 — Mon, 02 Mar 2020 09:30:41 GMT

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!!

Re: How to retain the values

ed_sas_member — Mon, 02 Mar 2020 09:46:13 GMT

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;

Re: How to retain the values

PeterClemmensen — Mon, 02 Mar 2020 09:51:02 GMT

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;

Re: How to retain the values

ed_sas_member — Mon, 02 Mar 2020 09:50:11 GMT

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;

Re: How to retain the values

Kurt_Bremser — Mon, 02 Mar 2020 10:20:50 GMT

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

Re: How to retain the values

sasuser123123 — Mon, 02 Mar 2020 10:33:51 GMT

It depends on condition that they would give...

Like average or sum of that values

Re: How to retain the values

PeterClemmensen — Mon, 02 Mar 2020 10:36:36 GMT

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;

Re: How to retain the values

sasuser123123 — Mon, 02 Mar 2020 10:40:13 GMT

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

Re: How to retain the values

PeterClemmensen — Mon, 02 Mar 2020 10:56:15 GMT

@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

Re: How to retain the values

sasuser123123 — Mon, 02 Mar 2020 14:45:04 GMT

Thank you so much for your assistance