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

Hi, I need your help to complete a dataset. I have many stock shares ordered by share and date. Also I have its last price of the day, but sometimes there is a missing value if there was no trading that day. I want to complete missing values with the last valid value. The amount of missing values are different by each stock shares. Thanks in advance for your help.

 

This is an example of my dataset:

 

Name Date Last

AAA 4/1/16 5.10

AAA 5/1/16 .

AAA 6/1/16 .

AAA 7/1/16 .

AAA 8/1/16 5.13

AAA 11/1/16 5.13

AAA 12/1/16 5.15

BBB 4/1/16 25.60

BBB 5/1/16 25.61

BBB 6/1/16 .

BBB 7/1/16 .

BBB 8/1/16 25.65

BBB 11/1/16 .

BBB 12/1/16 25.66

 

I need this:

For the second register, last price is missing, so I need to replace it with the previous value 5.10. The third register also has no value for last, so I replace with the previous value 5.10 and then until find a valid value. 

 

Name Date Last

AAA 4/1/16 5.10

AAA 5/1/16 5.10

AAA 6/1/16 5.10

AAA 7/1/16 5.10

AAA 8/1/16 5.13

AAA 11/1/16 5.13

AAA 12/1/16 5.15

BBB 4/1/16 25.60

BBB 5/1/16 25.61

BBB 6/1/16 25.61

BBB 7/1/16 25.61

BBB 8/1/16 25.65

BBB 11/1/16 25.65

BBB 12/1/16 25.66

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

Don't forget to take care of the case where last is missing for the first record of a stock

 

data have;
input Name $ Date :mmddyy10. Last;
format date yymmdd10.;
datalines;
AAA 4/1/16 5.10
AAA 5/1/16 .
AAA 6/1/16 .
AAA 7/1/16 .
AAA 8/1/16 5.13
AAA 11/1/16 5.13
AAA 12/1/16 5.15
BBB 3/1/16 .
BBB 4/1/16 25.60
BBB 5/1/16 25.61
BBB 6/1/16 .
BBB 7/1/16 .
BBB 8/1/16 25.65
BBB 11/1/16 .
BBB 12/1/16 25.66
;

data want (drop=lstlast);
  set have; by name notsorted;
  retain lstlast;
  if first.name then call missing(lstLast);
  if last ne . then lstlast=last;
  else last=lstlast;
run;

proc print data=want noobs; run;
PG

View solution in original post

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, not typing that data in, so this is untested:

data want (drop=lstlast);
  set have;
  retain lstlast;
  if last ne . then lstlast=last;
  else last=lstlast;
run;
fri0
Quartz | Level 8
The SAS server is down, but I'll try when it'll be operative again.
fri0
Quartz | Level 8
Thanks RW9, this work perfect when there is no missing space as first value for last. But, there were cases with first value missing, so I had to chose the PGSTats.
FreelanceReinh
Jade | Level 19

Hi @fri0,

 

A nice trick that I've learned from @data_null__ and @Astounding is to use the UPDATE statement as follows:

data have;
input Name $ Date :ddmmyy. Last;
format date ddmmyy8.;
cards;
AAA 4/1/16 5.10
AAA 5/1/16 .
AAA 6/1/16 .
AAA 7/1/16 .
AAA 8/1/16 5.13
AAA 11/1/16 5.13
AAA 12/1/16 5.15
BBB 4/1/16 .
BBB 5/1/16 25.61
BBB 6/1/16 .
BBB 7/1/16 .
BBB 8/1/16 25.65
BBB 11/1/16 .
BBB 12/1/16 25.66
;

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

Please note that I've replaced the first value of variable LAST for name 'BBB' with a missing value to demonstrate that with the above solution values from one name are not carried forward into missing data of another name.

 

Edit: It should be noted that the action of the UPDATE statement is not limited to variable LAST. This means that also missing DATE values would be filled with the last available date within the BY group.

data_null__
Jade | Level 19

@FreelanceReinhard wrote:

Hi @fri0,

 

Edit: It should be noted that the action of the UPDATE statement is not limited to variable LAST. This means that also missing DATE values would be filled with the last available date within the BY group.


That is true but there are ways to work around that.

  1. Initalize to missing the variable that you don't way to carry forward after the output statement.
  2. Only operate on (UPDATE) the LOCF variable(s) and get the others from SET.  KEEP or DROP depending on which is the shortest list.
data patients; 
   input patientID $ year $ diagA $ diagB $ diagC $; 
   datalines; 
1 2010 . . . 
1 2011 . 1 . 
1 2012 . . 1 
1 2014 . . . 
2 2009 1 . . 
2 2010 1 . . 
2 2013 . 1 . 
2 2015 . . . 
;;;; 
   run;
%let locf=patientid diag:;
data patients;
   if 0 then set patients;
   update patients(obs=0 keep=&locf) patients(keep=&locf);
   by patientid;
   set patients(drop=&locf);
   output;
   run;
proc print;
   run;

 

PGStats
Opal | Level 21

Don't forget to take care of the case where last is missing for the first record of a stock

 

data have;
input Name $ Date :mmddyy10. Last;
format date yymmdd10.;
datalines;
AAA 4/1/16 5.10
AAA 5/1/16 .
AAA 6/1/16 .
AAA 7/1/16 .
AAA 8/1/16 5.13
AAA 11/1/16 5.13
AAA 12/1/16 5.15
BBB 3/1/16 .
BBB 4/1/16 25.60
BBB 5/1/16 25.61
BBB 6/1/16 .
BBB 7/1/16 .
BBB 8/1/16 25.65
BBB 11/1/16 .
BBB 12/1/16 25.66
;

data want (drop=lstlast);
  set have; by name notsorted;
  retain lstlast;
  if first.name then call missing(lstLast);
  if last ne . then lstlast=last;
  else last=lstlast;
run;

proc print data=want noobs; run;
PG
fri0
Quartz | Level 8
Thans PG, this work absolutely perfect.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 2565 views
  • 8 likes
  • 5 in conversation