topic Re: Alternative to Proc Expand in SAS Programming

Alternative to Proc Expand

bouchbnz — Fri, 11 May 2018 14:39:34 GMT

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.

Re: Alternative to Proc Expand

art297 — Fri, 11 May 2018 14:52:35 GMT

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

Art, CEO, AnalystFinder.com

Re: Alternative to Proc Expand

bouchbnz — Fri, 11 May 2018 15:24:36 GMT

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.

Re: Alternative to Proc Expand

data_null__ — Fri, 11 May 2018 16:07:02 GMT

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

Re: Alternative to Proc Expand

bouchbnz — Fri, 11 May 2018 16:20:12 GMT

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!

Re: Alternative to Proc Expand

data_null__ — Fri, 11 May 2018 16:39:14 GMT

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

Re: Alternative to Proc Expand

bouchbnz — Fri, 11 May 2018 17:41:41 GMT

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.

Re: Alternative to Proc Expand

data_null__ — Fri, 11 May 2018 17:57:12 GMT

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;

Re: Alternative to Proc Expand

bouchbnz — Fri, 11 May 2018 19:15:59 GMT

Thank you data_null__! I will try and see!

Thank you ever so much.

Re: Alternative to Proc Expand

bouchbnz — Sun, 13 May 2018 10:48:08 GMT

Sorry for the late reply data_null__! It worked.

Thank you ever so much!