For example, I am trying to see how often variable Z changes in the X group
X | Y | Z |
---|---|---|
A | 201401 | 600 |
A | 201402 | 600 |
A | 201403 | 750 |
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
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;
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;
Thanks alot
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;
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.
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.