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;
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.
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.
Thank you,
It works perfectly.
HHC
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
I edited the code to drop the extraneous where filter. Try the revised code.
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.