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 |
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;
What if the last value in a by-group is missing? I.e what if the value for USA, 03MAR2019 is missing?
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;
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.