11-13-2012 09:33 AM
In a data set, I would like to compare a column value in one record with a column value in another record in the same data set (probably the previous record in the data set).
Is there a way to do this?
11-13-2012 09:40 AM
Paul: Question too vague. Provide example data and desired result. What is the maximum number of records that might separate the ones you want to compare? Are they within the same IDs? What are the maximum number of records for an ID? And, do you need to use the value or just need to know if it was present?
BTW: You have a number of current threads that still appear as unanswered. If they have been answered, please mark them accordingly so people don't spend unnecessary time trying to provide answers and solutions.
11-20-2012 01:52 PM
You can do it by the following:
by cnty_name startyear exit agecat4 exitMonthCategory;
if exit ne "ZTC";
if first.agecat4 then CumulativeNumber=0;
CumulativeNumber + COUNT;
11-20-2012 02:03 PM
There are two issues you will run into with this program.
1. The subsetting IF statement may be deleting the first record for a group, the one where first.agecat4 is true. In that case, your CumulativeNumber values will be thrown off. At a minimum, you need to set CumulativeNumber to 0 BEFORE subsetting any observations. Or, change IF to WHERE.
2. What if there are three or more observations in a BY group? CumulativeNumber will be the sum of all COUNT values so far, for that BY group. It's not the value from the previous observation.
It sounds like you have a lot of useful pieces there, but it doesn't sound like you have a final solution yet.
11-20-2012 02:32 PM
However, while I don't disagree with your recommendation to move the initialization up a line, I don't think that would pose a problem for him with the code he proposed. His by statement was "by cnty_name startyear exit agecat4 exitMonthCategory;" and his statement referred to agecat4, thus all records with exit eq "ZTC" would be deleted and his condition would still do what he wanted.
My only recommendation would be that he not rewrite the same file within the datastep, unless the exit eq "ZTC" records shouldn't exist in his data. Obviously, after running the proposed code, they would no longer exist.
11-20-2012 02:51 PM
Yes, you're right. I didn't notice that EXIT appeared earlier on the list of BY variables. Must be preoccupied with the thought of 12 guests coming for Thanksgiving!
11-20-2012 02:55 PM
Hope that you and everyone has a nice thanksgiving. We already celebrated Canadian thanksgiving, last month, but I'm trying to talk my wife into a second turkey.