topic Re: filling in missing values with respect to the values of another variable in SAS Programming

filling in missing values with respect to the values of another variable

aiste — Thu, 17 Dec 2020 17:52:17 GMT

Hello everyone,

I am adding a picture to illustrate my question - I have many different ISIN values, dates and ExecPrices. My question is, when it is sorted out by ISIN and DATE, how can I fill in the missing values of ExecPrice with the previous values of ExecPrice only and only if the dates are concerning the same ISIN value? For example in the attached picture, I would want the empty spaces of ExecPrice to be filled with the previous value. But I want it to be filled only because ISIN value is the same.

I tried to use another solution to the missing value problem suggested in this forum but this way of filling in the blank spaces does not stop if the ISIN number changes.

data want;
set have;
retain _ExecPrice;
if not missing(ExecPrice) then _ExecPrice=ExecPrice;
else ExecPrice=_ExecPrice;
drop _ExecPrice;
run;

Thank you in advance for your help!

Re: filling in missing values with respect to the values of another variable

novinosrin — Thu, 17 Dec 2020 18:29:21 GMT

You need to do BY GROUP processing-


data want;
set have;
by isin date;
retain _ExecPrice;
if first.date then call missing(_ExecPrice);
if not missing(ExecPrice) then _ExecPrice=ExecPrice;
else ExecPrice=_ExecPrice;
drop _ExecPrice;
run;

Re: filling in missing values with respect to the values of another variable

novinosrin — Thu, 17 Dec 2020 18:32:00 GMT

Alternatively, you could use UPDATE instead of RETAIN gymnastics-

data want;
 update have(obs=0) have;
 by isin date;
 output;
run;

Re: filling in missing values with respect to the values of another variable

aiste — Tue, 09 Feb 2021 09:48:04 GMT

thank you, that worked!

Re: filling in missing values with respect to the values of another variable

bherrin — Wed, 06 Apr 2022 00:31:04 GMT

Hello,

I have a similar problem to the one 'aiste' posted. I tried following the code that worked for them, but it doesn't do what I want. Below is a print of the 27 obs. 'Code' and 'ctryno' identify countries. I want to fill in missing values for 'ihdi' with the previous one but only for the same value of 'code'. I understand that this can't be done for first.code (which is year 2010) if that value is missing, but Given the 2010 value of 'Ango', I want the 2011 value to be the same. Same for 2011-2013 for "Beli'. Here is my last code attempt.

data four; set three;
by ctryno year;
retain _ihdi;
if first.ctryno then call missing(_ihdi);
if not missing(ihdi) then _ihdi=ihdi;
else ihdi=_ihdi;
run;

Thanks in advance for any help.

Re: filling in missing values with respect to the values of another variable

novinosrin — Wed, 06 Apr 2022 14:22:10 GMT

Hi @bherrin Thank you for reaching out. Could you please post a sample data of what you "HAVE" that I can copy/paste to my SAS/SQL client and the expected "OUTPUT(WANT)" for me to work with. Thank you!

Re: filling in missing values with respect to the values of another variable

bherrin — Thu, 07 Apr 2022 23:10:32 GMT

Hello @novinosrin. Thank you for the reply. Here is the code I used to generate the subset of PROC PRINT shown below. But what I want is the ihdi value at obs=8 to fill in obs=9 and 10, the last year for ctryno=1. I also want ihdi=0.311 at obs=21 to fill in obs=22. Similarly throughout the data file. Thanks again.

data want;

retain country code year ctryno;

set have;

by ctryno year;

retain _ihdi;

if first.code then call missing(_ihdi);

if not missing(ihdi) then _ihdi=ihdi;

else ihdi=_ihdi;

drop _ihdi;

proc print data=want;

var code ctryno year ihdi hdi;