Hello SAS community,
I hope you are well.
I am trying to create fill-in missing variables with their most recent values. The way I am trying to do it now is using this code:
proc expand data=Price1 out=PriceDay
to=second method=step;
id time;
run;
But since I do no longer have SAS/ETS, so I won't be able to use proc expand.
Any suggestions for an alternative?
Thank you very much in advance.
B.
@bouchbnz wrote:
Hello,
Thank you very much for your response.
Here is a sample of my data:
date time p1 p2 p3 01/01/2016 08:02:01 3829 . . 01/01/2016 08:02:46 . . . 01/01/2016 09:02:46 3828 1928 . 02/01/2016 08:04:01 . . . 02/01/2016 08:10:12 . . . 02/01/2016 10:10:04 3822 1927 . 02/01/2016 13:02:46 . . . 03/01/2016 14:02:46 3822 . . 03/01/2016 15:02:46 . 1927 1380 03/01/2016 16:02:46 3826 . .
I want to fill in the "." with the last observed value for p1 - p3. I actually have around 5891 variables (p1-p5981).
Thank you!
B.
If I understand correctly you are not filling in missing time (adding obs) just LOCFing the P: variables.
If that is the case the update trick should work for this.
data have;
input date :mmddyy. time :time8. p1 p2 p3;
format date mmddyy. time time.;
retain id 1;
cards;
01/01/2016 08:02:01 3829 . .
01/01/2016 08:02:46 . . .
01/01/2016 09:02:46 3828 1928 .
02/01/2016 08:04:01 . . .
02/01/2016 08:10:12 . . .
02/01/2016 10:10:04 3822 1927 .
02/01/2016 13:02:46 . . .
03/01/2016 14:02:46 3822 . .
03/01/2016 15:02:46 . 1927 1380
03/01/2016 16:02:46 3826 . .
;;;;
run;
proc print;
run;
data want;
update have(obs=0 keep=id) have;
by id;
output;
run;
proc print;
run;
You can find some alternatives in the post at: http://www.sascommunity.org/planet/blog/category/proc-expand/
Art, CEO, AnalystFinder.com
Hello Art,
Thanks for the quick response. It will definitely be useful since I am planning to do MA calculations.
I am not too familiar with PROC SQL, though. I am not too sure how I could fill in my data with the last recorded values using PROC SQL.
Would it be similar to data ... set ... retain... if not missing... procedure?
Thanks.
B.
@bouchbnz wrote:
Hello SAS community,
I hope you are well.
I am trying to create fill-in missing variables with their most recent values. The way I am trying to do it now is using this code:
proc expand data=Price1 out=PriceDay to=second method=step; id time; run;
But since I do no longer have SAS/ETS, so I won't be able to use proc expand.
Any suggestions for an alternative?
Thank you very much in advance.
B.
Show an example of your data.
Hello,
Thank you very much for your response.
Here is a sample of my data:
date | time | p1 | p2 | p3 |
01/01/2016 | 08:02:01 | 3829 | . | . |
01/01/2016 | 08:02:46 | . | . | . |
01/01/2016 | 09:02:46 | 3828 | 1928 | . |
02/01/2016 | 08:04:01 | . | . | . |
02/01/2016 | 08:10:12 | . | . | . |
02/01/2016 | 10:10:04 | 3822 | 1927 | . |
02/01/2016 | 13:02:46 | . | . | . |
03/01/2016 | 14:02:46 | 3822 | . | . |
03/01/2016 | 15:02:46 | . | 1927 | 1380 |
03/01/2016 | 16:02:46 | 3826 | . | . |
I want to fill in the "." with the last observed value for p1 - p3. I actually have around 5891 variables (p1-p5981).
Thank you!
B.
@bouchbnz wrote:
Hello,
Thank you very much for your response.
Here is a sample of my data:
date time p1 p2 p3 01/01/2016 08:02:01 3829 . . 01/01/2016 08:02:46 . . . 01/01/2016 09:02:46 3828 1928 . 02/01/2016 08:04:01 . . . 02/01/2016 08:10:12 . . . 02/01/2016 10:10:04 3822 1927 . 02/01/2016 13:02:46 . . . 03/01/2016 14:02:46 3822 . . 03/01/2016 15:02:46 . 1927 1380 03/01/2016 16:02:46 3826 . .
I want to fill in the "." with the last observed value for p1 - p3. I actually have around 5891 variables (p1-p5981).
Thank you!
B.
If I understand correctly you are not filling in missing time (adding obs) just LOCFing the P: variables.
If that is the case the update trick should work for this.
data have;
input date :mmddyy. time :time8. p1 p2 p3;
format date mmddyy. time time.;
retain id 1;
cards;
01/01/2016 08:02:01 3829 . .
01/01/2016 08:02:46 . . .
01/01/2016 09:02:46 3828 1928 .
02/01/2016 08:04:01 . . .
02/01/2016 08:10:12 . . .
02/01/2016 10:10:04 3822 1927 .
02/01/2016 13:02:46 . . .
03/01/2016 14:02:46 3822 . .
03/01/2016 15:02:46 . 1927 1380
03/01/2016 16:02:46 3826 . .
;;;;
run;
proc print;
run;
data want;
update have(obs=0 keep=id) have;
by id;
output;
run;
proc print;
run;
Yes, exactly, I am need the last observed value carried forward.
Thank you. I will try it on my data and let you know. It might take time as I have a heavy data.
Thanks data_null__!
Much appreciated.
B.
Note if your data is grouped (BY variables) use those in the BY statement. This will reset the LOCFing each time the BY group changes. This also works to carry forward character variables.
If you have variables that you don't want to LOCF use the following example and read those variables with a SET statement.
data want;
if 0 then set have;
update have(obs=0 keep=id) have(drop=p2);
by id;
set have(keep=p2);
output;
run;
Thank you data_null__! I will try and see!
Thank you ever so much.
Sorry for the late reply data_null__! It worked.
Thank you ever so much!
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!
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.