BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
aiste
Fluorite | Level 6

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!

 Screenshot (35).png

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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;

View solution in original post

6 REPLIES 6
novinosrin
Tourmaline | Level 20

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;
novinosrin
Tourmaline | Level 20

Alternatively, you could use UPDATE instead of RETAIN gymnastics-

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

bherrin
Calcite | Level 5

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.

 

bherrin_0-1649204792978.png

 

novinosrin
Tourmaline | Level 20

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!

 

bherrin
Calcite | Level 5

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;

 

bherrin_0-1649372773029.png

 

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1014 views
  • 0 likes
  • 3 in conversation