How to check a record against a previous record

deleted_user — Fri, 18 May 2007 19:46:00 GMT

I need to , in the query portion of a query in a stored process in EG, be able to compare a current record agains a previous record.

i.e., If DataInField1 NE LAST.DataInField1 then ........

Does anyone have an idea or two that you'd be willing to share? Thanks!

Re: How to check a record against a previous record

Cynthia_sas — Fri, 18 May 2007 20:49:52 GMT

Hi,
Again, I'd find it helpful to understand the rest of the processing you need to do. However, in theory, let's look at how BY GROUP processing works within a SAS data step program (and put aside SQL query syntax right now). Consider this data which is a file of gift ideas, multiple ideas per person. Silly data, I know, but it helps to visualize how things work:
[pre]
data gifts;
length name $8 occasion $12 idea $8 amt 8;
infile datalines;
input name occasion idea amt;
return;
datalines;
cynthia anniversary jewelry 50
cynthia anniversary tunes 25
cynthia birthday books 50
cynthia birthday jewelry 75
cynthia christmas clothes 100
cynthia christmas jewelry 75
lee anniversary bikegear 50
lee anniversary books 50
lee any bikegear 50
lee any tunes 50
lee birthday tunes 50
lee birthday books 50
lee christmas books 100
lee christmas computer 50
sarah any tunes 25
sarah any money 50
sarah birthday jewelry 50
sarah birthday giftcard 50
sarah christmas tunes 25
sarah christmas jewelry 50
sarah graduation books 100
sarah graduation money 100
;
run;

ods listing;
proc print data=gifts;
title 'Before - All Rows';
run;

[/pre]

Now, let's say I want to do -something- with the gift file. I can "turn on" by group processing for the NAME column and then check the values of FIRST.NAME and LAST.NAME automatic variables that get created when I use the BY statement in a DATA step program:
[pre]
** show what first.byvar and last.byvar are;
data ckbyvar;
set gifts;
by name;
firstflag = first.name;
lastflag = last.name;
run;

proc print data=ckbyvar;
title 'What First.byvar and Last.byvar are for each obs';
run;

[/pre]

And this is the output. Note the changing values of the firstflag column and the lastflag column. This means that you KNOW that NAME
is the same (or different) for every OBS by testing the values for first.name and last.name.
For example, when first.name=1 then I know that this name is either different from the previous name or is the first name in the file (which I can test with _n_=1).
Usually, these variables are not output to a dataset -- which is why I had to capture them to other named variables.

[pre]
What First.byvar and Last.byvar are for each obs

Obs name occasion idea amt firstflag lastflag
1 cynthia anniversary jewelry 50 1 0
2 cynthia anniversary tunes 25 0 0
3 cynthia birthday books 50 0 0
4 cynthia birthday jewelry 75 0 0
5 cynthia christmas clothes 100 0 0
6 cynthia christmas jewelry 75 0 1
7 lee anniversary bikegear 50 1 0
8 lee anniversary books 50 0 0
9 lee any bikegear 50 0 0
10 lee any tunes 50 0 0
11 lee birthday books 50 0 0
12 lee birthday tunes 50 0 0
13 lee christmas books 100 0 0
14 lee christmas computer 50 0 1
15 sarah any money 50 1 0
16 sarah any tunes 25 0 0
17 sarah birthday giftcard 50 0 0
18 sarah birthday jewelry 50 0 0
19 sarah christmas jewelry 50 0 0
20 sarah christmas tunes 25 0 0
21 sarah graduation books 100 0 0
22 sarah graduation money 100 0 1
[/pre]

Then, if I want to just COUNT the "non-any" ideas per person (for some reason, I don't want to do this with a procedure), then I could do this:
[pre]
** count number of ideas per person;
data cntidea(keep=name totidea);
retain totidea;
set gifts;
by name;
if first.name then totidea = 0;
if occasion ne 'any' then totidea + 1;
if last.name then output;
run;

proc print data=cntidea;
title 'ideas per name';
run;
[/pre]

But, if what you want to do is ALWAYS compare a value on this record with the value on the previous record (without regard to by group processing), then you can do something like this:

[pre]
** just test same or different name;
data testname;
set gifts;
retain prevname;
if _n_ = 1 then do;
prevname = name;
flag = 'start';
end;
else if _n_ gt 1 then do;
if name = prevname then flag='same';
else do;
flag = 'diff';
prevname = name;
end;
end;
run;

proc print data=testname;
title 'set a single flag';
run;
[/pre]

This sets a single flag whose values are either "start", "same" or "diff" depending on whether the name is the same as the name on the last record or obs.

And, last, but not least, you could use the LAG function:
[pre]
** using LAG;
data uselag;
set gifts;
prevname = lag(name);
if _n_ = 1 then prevname = name;
run;

proc print data=uselag;
title 'Lag Output';
run;

[/pre]

Play around with these examples and the ones below and see whether any of them give you some ideas of how to do your testing.
cynthia

***more examples of BY group processing
[pre]

data firstbyname;
set gifts;
by name;
if first.name then output;
run;

proc print data=firstbyname;
title 'After -- choosing first.name ONLY';
run;

data firstbyname_occ;
set gifts;
by name occasion;
if first.occasion then output;
run;

proc print data=firstbyname_occ;
title 'After -- choosing first.occ';
title2 'Because BY is NAME and OCCASION, then';
title3 'will get the first of the unique name/occasion combo';
run;

proc sort data=gifts out=gifts;
by name idea;
run;

data firstbyname_idea;
set gifts;
by name idea;
if first.idea then output;
run;

proc print data=firstbyname_idea;
title 'After -- choosing first.idea';
title2 'Because BY is NAME and IDEA, then';
title3 'will get the first of the unique name/idea combo';
run;

title;

[/pre]

Re: How to check a record against a previous record

deleted_user — Fri, 18 May 2007 21:56:55 GMT

!!! How do you type so much so fast???? Thank you again and again for your knowledge and willingness to share with others. I really appreciate it. I'll digest what you wrote over the weekend.

Have a good weekend and thanks!

Re: How to check a record against a previous record

Cynthia_sas — Sun, 20 May 2007 16:33:29 GMT

Hi:
I only have to type the explanation -- I have the code in sample files that come from years of being an instructor. 😉 Although I -do- type fast, I don't type THAT fast.
cynthia

topic Re: How to check a record against a previous record in SAS Enterprise Guide

How to check a record against a previous record

Re: How to check a record against a previous record

Re: How to check a record against a previous record

Re: How to check a record against a previous record