BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
hhchenfx
Barite | Level 11

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;
1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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.

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

4 REPLIES 4
mkeintz
PROC Star

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.

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
hhchenfx
Barite | Level 11

Thank you,

It works perfectly.

HHC

hhchenfx
Barite | Level 11

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
PROC Star

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

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 4 replies
  • 552 views
  • 2 likes
  • 2 in conversation