How to run proc Expand for specific records

[hhchenfx]

Hi Everyone,

I want to run the proc expand for only date>=3 in the below example.

I try Where but it doesn't work correctly.

Can you please help?

Thanks,

HHC

data have;
input id date value;
datalines;
1 1 3
1 2 4
1 3 1
1 4 5
2 1 1
2 2 2
2 3 10
2 4 0
;run;

proc expand data=have out=temp2 METHOD=STEP;
by id ;
convert value		=SUM3/transformout=(MOVsum  3 );
run;

[mkeintz · Accepted solution]

If your dates are really 1, 2, 3, 4, ..., then you could use the WHERE filter on the output dataset, as in:

proc expand data=have out=temp2 (where=(date>=3)) METHOD=STEP;
by id ;
  convert value		=SUM3/transformout=(MOVsum 3);
run;

The above will produce 4 observations in the TEMP2 dataset.

Another alternative is to keep the extra observations for the first two dates of each ID, but to set SUM3=. for those observations, via the TRIMLEFT parameter in the CONVERT statement.  Edited: removed unnecessary where filter.

proc expand data=have out=temp2 METHOD=STEP;
 convert value		=SUM3/transformout=(MOVsum 3 trimleft 2);
by id ;
run;

The advantage of the TRIMLEFT option is that is it telling proc expand to only generate SUM3 when the moving window has 3 non-missing values.  You will get 8 observations, 4 with SUM3=. and 4 with non-missing values of SUM3.

Of course, you could use the TRIMLEFT option, and then add a "where=(sum^=.))" filter.  This would allow you to drop the first two obs for each ID, even if different ID's had different series of DATE values - no need to know what the DATE values are.

[hhchenfx]

Thank you,

It works perfectly.

HHC

[hhchenfx]

Hi mkeintz,

Some how the Trimleft code yield the only 4 record instead of 8.

Can you help to fix it to make SAS keep all original records while computing the sum of only date>=3?

Thank you,

HHC

316 proc expand data=have out=temp2 (where=(date>=3)) METHOD=STEP;
317 convert value =SUM3/transformout=(MOVsum 3 trimleft 2);
318 by id ;
319 run;

NOTE: The data set WORK.TEMP2 has 4 observations and 5 variables.
NOTE: PROCEDURE EXPAND used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

[mkeintz]

I edited the code to drop the extraneous where filter.  Try the revised code.