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.
if frequency=X and prev.frequency=next.frequency then frequency=prev.frequency;
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.
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??
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??
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’.”
input Acct $ Start :anydtdte10. End :anydtdte10. 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
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;
proc print data=temp1;
Message was edited by: polingjw