topic Re: data step: comparing 1 record value with a previous record value in SAS Programming

data step: comparing 1 record value with a previous record value

Paul_NYS — Tue, 13 Nov 2012 14:33:26 GMT

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?

Paul

Re: data step: comparing 1 record value with a previous record value

art297 — Tue, 13 Nov 2012 14:40:09 GMT

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.

Re: data step: comparing 1 record value with a previous record value

Paul_NYS — Tue, 20 Nov 2012 18:52:35 GMT

You can do it by the following:

data s1AgeYear1CumPERM;

set s1AgeYear1PERM;

by cnty_name startyear exit agecat4 exitMonthCategory;

if exit ne "ZTC";

if first.agecat4 then CumulativeNumber=0;

CumulativeNumber + COUNT;

run;

Re: data step: comparing 1 record value with a previous record value

art297 — Tue, 20 Nov 2012 19:01:57 GMT

Glad to see that you were able to answer the question on your own.

Re: data step: comparing 1 record value with a previous record value

Astounding — Tue, 20 Nov 2012 19:03:09 GMT

Paul,

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.

Good luck.

Re: data step: comparing 1 record value with a previous record value

art297 — Tue, 20 Nov 2012 19:32:12 GMT

: Actually, I think Paul accomplished what he wanted, using a different method, in a different thread.

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.

Re: data step: comparing 1 record value with a previous record value

Astounding — Tue, 20 Nov 2012 19:51:37 GMT

Art,

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!

Re: data step: comparing 1 record value with a previous record value

art297 — Tue, 20 Nov 2012 19:55:03 GMT

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.