DATA Step, Macro, Functions and more

Trying to use first. and last. processing in 3rd variable of a by statement

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 82
Accepted Solution

Trying to use first. and last. processing in 3rd variable of a by statement

     For example, I am trying to see how often variable Z changes in the X group

XYZ
A201401600
A201402600
A201403750

Variable X is in this example a particular loan, with Y being the monthend and Z being the score.

So lets say there are 10 identical Zs in a row and then the 11th observation is a different Z for the same X, I want to know that there were 10 in a row before changing.

I realize first.Z would be 1 for each Z because Y changes each observation (sorted by X Y Z).

I can get Z to add up correctly when I say if lag(Z)=Z then variable AA+1, but then I dont know how to output only the observation when AA changes back to one (when Z would have a new observation). Again, I cant use last. processing because Y changes each time, and we want the data in the order of Y.

Thanks for any help


Accepted Solutions
Solution
‎12-09-2014 11:17 AM
Super User
Posts: 19,772

Re: Trying to use first. and last. processing in 3rd variable of a by statement

Try it with the notsorted option? I'm not 100% sure what type of output you want so you can try it with the last. here and see if that provides what you need.

data want;

set have;

by x z notsorted;

if first.z then count=1; else count+1;

if last.z then output; *not sure this is what you want;

run;

View solution in original post


All Replies
Solution
‎12-09-2014 11:17 AM
Super User
Posts: 19,772

Re: Trying to use first. and last. processing in 3rd variable of a by statement

Try it with the notsorted option? I'm not 100% sure what type of output you want so you can try it with the last. here and see if that provides what you need.

data want;

set have;

by x z notsorted;

if first.z then count=1; else count+1;

if last.z then output; *not sure this is what you want;

run;

Frequent Contributor
Posts: 82

Re: Trying to use first. and last. processing in 3rd variable of a by statement

Thanks alot

PROC Star
Posts: 7,468

Re: Trying to use first. and last. processing in 3rd variable of a by statement

Similar to 's suggested code but, if you want AA to reflect the same count for all records having the same value of z within an x, then a double dow would do that kind of task:

data want;

  do until (last.z);

    set have;

    by x z notsorted;

    if first.z then AA=1;

    else AA+1;

  end;

   do until (last.z);

    set have;

    by x z notsorted;

    output;

  end;

run;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 306 views
  • 3 likes
  • 3 in conversation