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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

@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;

Capture.PNG 

View solution in original post

9 REPLIES 9
art297
Opal | Level 21

You can find some alternatives in the post at: http://www.sascommunity.org/planet/blog/category/proc-expand/

 

Art, CEO, AnalystFinder.com

 

bouchbnz
Calcite | Level 5

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.

 

 

 

 

data_null__
Jade | Level 19

@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.

bouchbnz
Calcite | Level 5

Hello,

 

Thank you very much for your response.

 

Here is a sample of my data:

datetimep1p2p3
01/01/201608:02:013829..
01/01/201608:02:46...
01/01/201609:02:4638281928.
02/01/201608:04:01...
02/01/201608:10:12...
02/01/201610:10:0438221927.
02/01/201613:02:46...
03/01/201614:02:463822..
03/01/201615:02:46.19271380
03/01/201616:02:463826..

 

 

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. 

data_null__
Jade | Level 19

@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;

Capture.PNG 

bouchbnz
Calcite | Level 5

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.

data_null__
Jade | Level 19

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;
bouchbnz
Calcite | Level 5

Thank you data_null__! I will try and see!

Thank you ever so much.

bouchbnz
Calcite | Level 5

Sorry for the late reply data_null__! It worked.

Thank you ever so much!

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 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
  • 9 replies
  • 2585 views
  • 2 likes
  • 3 in conversation