topic Re: Prev and Next objects in SAS Programming

Prev and Next objects

deleted_user — Thu, 30 Sep 2010 12:13:43 GMT

Question...I have a variable, Frequency, that represents a period of time for a specific record, either monthly (M) or quarterly (Q). It was created by using actual to/from dates in my data set, which is a list of sales reports by agent by reporting dates (eg. Jan 1, 2009 to Mar 31, 2009). I have few periods for a few agents that are neither monthly or quarterly (currently represented by X) (for example, account 111 submits sales every quarter, except for one, when she submitted it after 43 days..after this submission, she returned to submitting every quarter..., in other situations, the are for 2 days only as the person left the organization and this is the final submission)

I am trying to use prev. and next. steps (I have about a million records) to identify these odd periods...in situations where the odd record is the last one, the fix is simple (using the first. and last. objects) but I am trying to use prev. and next. objects to identify those agents that did not change their reporting period or leave the org...they just had one odd reporting period in their files.

data A_prod1;
set A_prod1;
by frequency;
if frequency=X and prev.frequency=next.frequency then frequency=prev.frequency;
run;

This is telling me that prev. is not an object, but I can see that it is...my issue is syntax I'm sure, but have no idea what to do.

Any help would be appreciated. Thanks

Re: Prev and Next objects

Cynthia_sas — Thu, 30 Sep 2010 17:25:21 GMT

Hi:
Can you point to a place in the documentation that discusses the use of PREV. and NEXT.?? I am not familiar with that syntax construction.

You say that you know that PREV.FREQUENCY and NEXT.FREQUENCY are available because you can "see that it is" -- how do you see PREV.FREQUENCY and NEXT.FREQUENCY??

When you use a BY statement, such as BY FREQUENCY, I would expect that the only automatic variables available to you would be FIRST.FREQUENCY and LAST.FREQUENCY. However, I would also expect, given your description of the data, that you would not want to deal with ALL the frequency counts in the data, but would want a further breakdown by AGENT...so I'd expect to see something like:
BY AGENT FREQUENCY;
instead of just BY FREQUENCY.

Where did the data come from??? Was it from a summary procedure like PROC FREQ or PROC MEANS??

Can you show a mini-subset of what your dataset A_prod1 looks like??? Or show how A_prod1 was created??

cynthia

Re: Prev and Next objects

deleted_user — Thu, 30 Sep 2010 18:13:58 GMT

Hi there...I assumed prev and next would function the same as first. and last., but I can see through the documentation it likely doesn't.

It's a raw data file...if you see in the pattern, the September 20th start period is out of the ordinary...in this situation, I am trying to populate the variable Freq for that period with an M.

Acct Start End Amount Days Freq SasDate
111 2009-03-22 2009-04-25 41131.54 34 M 17978
111 2009-04-26 2009-05-23 33244.07 27 M 18013
111 2009-05-24 2009-06-20 35213.05 27 M 18041
111 2009-06-21 2009-07-25 44396.78 34 M 18069
111 2009-07-26 2009-08-22 34813.28 27 M 18104
111 2009-08-23 2009-09-19 34883.25 27 M 18132
111 2009-09-20 2009-10-31 50514.43 41 0 18160
111 2009-11-01 2009-11-28 37537.01 27 M 18202
111 2009-11-29 2009-12-26 32868.09 27 M 18230
111 2009-12-27 2010-01-30 42070.19 34 M 18258
111 2010-01-31 2010-02-27 33186.61 27 M 18293
111 2010-02-28 2010-03-27 32800.06 27 M 18321
111 2010-03-28 2010-05-01 42657.51 34 M 18349
111 2010-05-02 2010-05-29 34714.33 27 M 18384
111 2010-05-30 2010-06-26 34715.91 27 M 18412

Re: Prev and Next objects

Cynthia_sas — Thu, 30 Sep 2010 18:39:07 GMT

Hi:
Does this mean that you already have a program that generates FREQUENCY??? Are ALL of these variables in your original RAW data file???
Acct Start End Amount Days Freq SasDate

Or, do you calculate DAYS, FREQ and SASDATE??? Or have they already been calculated? It sounds to me like you are calculating FREQ -- is that how some values are already set to 'M' and the value for Sept 20-Oct31 is set to 0??

If you calculate FREQ, can you post the code that you use to calculate FREQ??

cynthia

Re: Prev and Next objects

polingjw — Thu, 30 Sep 2010 19:01:13 GMT

To get the previous value, you need to use the lag function. Getting the next value is a little trickier. I’ve done that below by using a second set statement. The check against _n_ is needed to prevent the EOF marker from being prematurely encountered on the 2nd set statement. Your first post used the condition “if frequency = X.” I did not know what the variable X was so I changed the condition to “if freq ne ‘M’.”

[pre]
data temp;
input Acct $ Start :anydtdte10. End :anydtdte10. Amount Days Freq $ SasDate;
datalines;
111 2009-03-22 2009-04-25 41131.54 34 M 17978
111 2009-04-26 2009-05-23 33244.07 27 M 18013
111 2009-05-24 2009-06-20 35213.05 27 M 18041
111 2009-06-21 2009-07-25 44396.78 34 M 18069
111 2009-07-26 2009-08-22 34813.28 27 M 18104
111 2009-08-23 2009-09-19 34883.25 27 M 18132
111 2009-09-20 2009-10-31 50514.43 41 0 18160
111 2009-11-01 2009-11-28 37537.01 27 M 18202
111 2009-11-29 2009-12-26 32868.09 27 M 18230
111 2009-12-27 2010-01-30 42070.19 34 M 18258
111 2010-01-31 2010-02-27 33186.61 27 M 18293
111 2010-02-28 2010-03-27 32800.06 27 M 18321
111 2010-03-28 2010-05-01 42657.51 34 M 18349
111 2010-05-02 2010-05-29 34714.33 27 M 18384
111 2010-05-30 2010-06-26 34715.91 27 M 18412
;;
run;

data temp1;
set temp nobs=n;
if _n_ ne (n-1) then set temp(firstobs=2 keep=freq rename=(freq=nextfreq));
lastfreq = lag(freq);
if _n_ = n then nextfreq = '';

if freq ne 'M' and lastfreq = nextfreq then freq = lastfreq;
run;

proc print data=temp1;
run;
[/pre] Message was edited by: polingjw

Re: Prev and Next objects

art297 — Thu, 30 Sep 2010 22:01:00 GMT

Some similar methods for accomplishing the same thing can be found at:
http://www.sascommunity.org/wiki/Look-Ahead_and_Look-Back>/url>

HTH,
Art

Re: Prev and Next objects

deleted_user — Fri, 01 Oct 2010 10:31:02 GMT

Thanks a lot...Ill give this a try.

Cynthia, you are correct, I created days, freq and sas_date.