Alternative to Proc Expand

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Alternative to Proc Expand

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.


Accepted Solutions
Solution
‎05-13-2018 06:47 AM
Respected Advisor
Posts: 3,852

Re: Alternative to Proc Expand

[ Edited ]

@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


All Replies
PROC Star
Posts: 8,167

Re: Alternative to Proc Expand

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

 

Art, CEO, AnalystFinder.com

 

Occasional Contributor
Posts: 6

Re: Alternative to Proc Expand

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.

 

 

 

 

Respected Advisor
Posts: 3,852

Re: Alternative to Proc Expand


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

Occasional Contributor
Posts: 6

Re: Alternative to Proc Expand

Posted in reply to data_null__

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. 

Solution
‎05-13-2018 06:47 AM
Respected Advisor
Posts: 3,852

Re: Alternative to Proc Expand

[ Edited ]

@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 

Occasional Contributor
Posts: 6

Re: Alternative to Proc Expand

Posted in reply to data_null__

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.

Respected Advisor
Posts: 3,852

Re: Alternative to Proc Expand

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;
Occasional Contributor
Posts: 6

Re: Alternative to Proc Expand

Posted in reply to data_null__

Thank you data_null__! I will try and see!

Thank you ever so much.

Occasional Contributor
Posts: 6

Re: Alternative to Proc Expand

Posted in reply to data_null__

Sorry for the late reply data_null__! It worked.

Thank you ever so much!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 209 views
  • 2 likes
  • 3 in conversation