CODEWITHSUNDEEP
sasdatastep
user2941280
user2941280

Reputation: 295

SAS - Compare observation to previous observations?

I have the following data:

acct        date
11111       01/01/2014
11111       01/01/2014
11111       02/02/2014
22222       01/01/2014
22222       01/01/2014
33333       01/01/2013
33333       03/03/2014
44444       01/01/2014
44444       01/01/2014
44444       01/01/2014

What would be the best way to accomplish the following in SAS? I want to compare the dates for each acct number and return all the records for the accts where there is at least one date that doesn't match.

So for the dataset above, I want to end up with the following:

acct        date
11111       01/01/2014
11111       01/01/2014
11111       02/02/2014
33333       01/01/2013
33333       03/03/2014

Upvotes: 2

Views: 5617

Answers (2)

Laurent de Walick
Laurent de Walick

Reputation: 2174

A single PROC SQL will do the trick. Use count(distinct date) to count the number of different dates. Group that by acct to do the count by acct and when the result is greater than 1 filter it using a having clause. Next select acct and date as output columns.

This is SAS specific handling of SQL. Most other implementation will not allow this construct where you don't put all non-aggregate columns from the select in the group by clause.

proc sql noprint;
    create table _output as
    select acct, date format=ddmmyys10.
    from _input
    group by acct
    having count(distinct date) > 1
    order by acct, date;
   quit;

Upvotes: 2

Joe
Joe

Reputation: 63424

Something like this would work. Sort your data by acct/date if not already, then check each last.date row. If the first last.date row is not also last.acct, then it is a set of rows where the respondent needs to be output. Here I only output one row per date/acct combination:

data want;
set have;
by acct date;
if (last.date) and not (last.acct) then do;
  flg=1;
  output;
end;
else if last.date and flg=1 then output;
else if first.acct then flg=0;
run;

If you need all rows, then you need to either take the above and merge it back to the original, or you could do a DoW loop:

data want;
do _n_=1 by 1 until (last.acct);
 set have;
 by acct date;
 if (last.date) and not (last.acct) then do;
  flg=1;
 end;
end;
do _t_ = 1 by 1 until (last.acct);
 set have;
 by acct date;
 if flg=1 then output;
end;
run;

Upvotes: 1

Related Questions