BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
6 REPLIES 6
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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
Cynthia_sas
SAS Super FREQ
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
polingjw
Quartz | Level 8
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
art297
Opal | Level 21
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
deleted_user
Not applicable
Thanks a lot...Ill give this a try.

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 6 replies
  • 726 views
  • 0 likes
  • 4 in conversation