DATA Step, Macro, Functions and more

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

Reply
Regular Contributor
Posts: 216

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

Hi

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

PROC Star
Posts: 7,468

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

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.

Regular Contributor
Posts: 216

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

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;

PROC Star
Posts: 7,468

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

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

Super User
Posts: 5,498

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

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.

PROC Star
Posts: 7,468

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

Posted in reply to Astounding

: 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.

Super User
Posts: 5,498

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

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!

PROC Star
Posts: 7,468

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

Posted in reply to Astounding

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.

Ask a Question
Discussion stats
  • 7 replies
  • 423 views
  • 3 likes
  • 3 in conversation