## Difference between values in two rows

Solved
Occasional Contributor
Posts: 7

# Difference between values in two rows

HOW TO CALCULATE THE DIFFERENCE WITH IN THE COLUMNS LIKE "GAP DIFFERENCE" = (2ND ROW VALUE -1 ST ROW VALUE) FOR n NUMBER OF SUBSEQUENT ROWS.

Accepted Solutions
Solution
‎06-29-2017 04:44 AM
Super User
Posts: 10,530

## Re: Difference between values in two rows

That's simply because

`Pyrometer Channel01`

is not a valid SAS name.

The dif() function only accepts one variable (missed that when I copied your code).

AND PLEASE DO NOT POST TEXT AS PICTURES!

Use the {i} button (the 6th on top of the posting window) to post text from logs etc.

The 7th button is good for SAS code.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

All Replies
Occasional Contributor
Posts: 7

1
2 1
3 2
4 3
5 4
6 5
6
Super User
Posts: 13,889

## Re: Difference between values in two rows

You really need to indicate the input data separately from the output. We can't tell if you may be adding rows or not;

It appears that you are actually comparing to the first value not to the previous value. In which case you need to keep the first value.

Here is one way to do at least part of what I think you are attempting. Notice the use of a different sequence of values. Your orginal example has many other ways to create without involving any other row in any way.

```data have;
input value;
datalines;
1
2
5
4
9
6
;
run;

data want;
set have;
retain base;
if _n_=1 then base=value;
else difference = value-base;
drop base;
run;```

The _n_ is a special variable related to the row of the data set. In this case when = 1 then it is the first row and we set the base value to keep.

Super User
Posts: 10,530

## Re: Difference between values in two rows

Please don't shout at us (writing all capitals).

To compare with the preceding observation, use the lag() function.

To compare with a specific previous value, use a retained variable that is set whenever a new group starts.

For further help, please post example data in a data step like this:

``````data want;
input value;
cards;
1
2
3
4
5
6
;
run;``````
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 9,799

## Re: Difference between values in two rows

Please dont post all in upper case.  Are you using code for this?  If so then you can do two things:

retain:  retain values across rows - in this example val1 will contain the first val data across the dataset.

```data want;
set have;
retain val1;
if _n_=1 then val1=val;
run;```

lagX() function: with this you can look back X number of rows:

```data want;
set have;
test=value-lag5(value);
run;```
Super User
Posts: 6,903

## Re: Difference between values in two rows

The simplest way:

data want;

set have;

difference = dif(varname);

run;

One trick to the DIF function:  apply it on every observation.  Even if you don't want DIFFERENCE calculated all the time, apply it anyway.  You can always wipe out DIFFERENCE later if you need to with:

if /* some condition here */ then difference = .;

Occasional Contributor
Posts: 7

## Re: Difference between values in two rows

only on SAS Enterprise guide.... without code

Occasional Contributor
Posts: 7

## Re: Difference between values in two rows

thank you for the reply. Since I am new in code in sas . can you do other methodology in sas enterprise guide using query builder and write an expression  to get the result .

problem  description: I have one column with

 Value 150 160 180 200 250
 We need to generate a new column called Difference as the following table
 Value Difference 150 160 10 180 20 200 20 250 50

Occasional Contributor
Posts: 7

## Re: Difference between values in two rows

data want;
WORK.QUERY_FOR_APPEND_TABLE_0002;
difference = dif(Pyrometer Channel01);
run;

error

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24
25 GOPTIONS ACCESSIBLE;
26 data want;
27 WORK.QUERY_FOR_APPEND_TABLE_0002;
________________________________
557
ERROR: DATA STEP Component Object failure. Aborted during the COMPILATION phase.
ERROR 557-185: Variable WORK is not an object.

Super User
Posts: 10,530

## Re: Difference between values in two rows

You missed the set keyword in your code:

``````data want;
set work.query_for_append_table_0002;
difference = dif(Pyrometer Channel01);
run;``````
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 7

## Re: Difference between values in two rows

still has some problem. Please check

Solution
‎06-29-2017 04:44 AM
Super User
Posts: 10,530

## Re: Difference between values in two rows

That's simply because

`Pyrometer Channel01`

is not a valid SAS name.

The dif() function only accepts one variable (missed that when I copied your code).

AND PLEASE DO NOT POST TEXT AS PICTURES!

Use the {i} button (the 6th on top of the posting window) to post text from logs etc.

The 7th button is good for SAS code.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 7