BookmarkSubscribeRSS Feed
Paul_NYS
Obsidian | Level 7

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

7 REPLIES 7
art297
Opal | Level 21

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.

Paul_NYS
Obsidian | Level 7

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;

art297
Opal | Level 21

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

Astounding
PROC Star

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.

art297
Opal | Level 21

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

Astounding
PROC Star

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!

art297
Opal | Level 21

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1527 views
  • 3 likes
  • 3 in conversation