topic how assign missing values when first occurence or last occurence groups are missed in SAS Programming

how assign missing values when first occurence or last occurence groups are missed

thanikondharish — Sun, 17 Mar 2019 10:55:14 GMT

country	date	amount
ind	01MAR2019	345
ind	02MAR2019	.
ind	03MAR2019	463
aus	01MAR2019	.
aus	02MAR2019	357
aus	03MAR2019	500
usa	01MAR2019	345
usa	02MAR2019	222
usa	03MAR2019	463

I have above dataset like 3 groups one groups has first record value is missing so in that place value should be like last record value in that group and

one group has somewhere values missing so assign first occurence value like see below dataset

country	date	amount
ind	01MAR2019	345
ind	02MAR2019	345
ind	03MAR2019	463
aus	01MAR2019	500
aus	02MAR2019	357
aus	03MAR2019	500
usa	01MAR2019	345
usa	02MAR2019	222
usa	03MAR2019	463

Re: how assign missing values when first occurence or last occurence groups are missed

Ksharp — Sun, 17 Mar 2019 11:35:45 GMT

data have;
input country $
date : $20.
amount ;
cards;
ind
01MAR2019
345
ind
02MAR2019
.
ind
03MAR2019
463
aus
01MAR2019
.
aus
02MAR2019
357
aus
03MAR2019
500
usa
01MAR2019
345
usa
02MAR2019
222
usa
03MAR2019
463
;

data temp;
 set have;
 by country notsorted;
 retain new_amount;
 if first.country then call missing(new_amount);
 if not missing(amount) then new_amount=amount;
run;
data temp(index=(country));
 set temp;
 by country new_amount notsorted;
 if first.country then n=0;
 n+first.new_amount;
run;
data k;
 set temp;
 by country;
 if last.country;
 keep country new_amount;
run;
data want;
 merge temp k(rename=(new_amount=_amount));
 by country;
 if missing(new_amount) and n=1 then new_amount=_amount;
 drop n _amount;
run;

Re: how assign missing values when first occurence or last occurence groups are missed

PeterClemmensen — Sun, 17 Mar 2019 12:57:25 GMT

What if the last value in a by-group is missing? I.e what if the value for USA, 03MAR2019 is missing?

Re: how assign missing values when first occurence or last occurence groups are missed

Patrick — Sun, 17 Mar 2019 22:42:56 GMT

Below should implement the logic you're after.


data want(drop=_:);
  do until(last.country);
    set have;
    by country notsorted;
    _first_nonMiss_amount=coalesce(_first_nonMiss_amount,amount);
    _last_non_Miss_amount =coalesce(amount,_last_non_Miss_amount);
  end;

  do until(last.country);
    set have;
    by country notsorted;
    if missing(amount) then
      do;
        if first.country then amount=_last_non_Miss_amount;
        else amount=_first_nonMiss_amount;
      end; 
    output;
  end;
  call missing(_first_nonMiss_amount, _last_non_Miss_amount);
run;

Re: how assign missing values when first occurence or last occurence groups are missed

s_lassen — Mon, 18 Mar 2019 12:53:19 GMT

I would use a SET statement with POINT= to look forward:

proc sort data=have;
  by country date;
run;

data want;
  set have nobs=nobs;
  by country;
  if first.country or not missing(amount) then
    prev_amount=amount;
  retain prev_amount;
  amount=coalesce(amount,prev_amount);
  if not last.country then do _P_=_N_+1 to nobs while(missing(amount));
    set have(drop=date rename=(amount=next_amount country=next_country)) point=_P_;
    if country ne next_country then leave;
    amount=coalesce(amount,next_amount);
    end;
  drop prev_: next_:;
run;