topic Re: Difference between values in two rows in SAS Enterprise Guide

Difference between values in two rows

mrinmoynano — Wed, 28 Jun 2017 11:29:30 GMT

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.

Re: Difference between values in two rows

mrinmoynano — Wed, 28 Jun 2017 11:33:11 GMT

1
2 1
3 2
4 3
5 4
6 5
6

Re: Difference between values in two rows

Kurt_Bremser — Wed, 28 Jun 2017 11:41:52 GMT

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;

Re: Difference between values in two rows

RW9 — Wed, 28 Jun 2017 11:43:18 GMT

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;

Re: Difference between values in two rows

Astounding — Wed, 28 Jun 2017 12:10:28 GMT

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 = .;

Re: Difference between values in two rows

ballardw — Wed, 28 Jun 2017 19:50:05 GMT

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.

Re: Difference between values in two rows

mrinmoynano — Thu, 29 Jun 2017 03:23:00 GMT

only on SAS Enterprise guide.... without code

Re: Difference between values in two rows

mrinmoynano — Thu, 29 Jun 2017 05:14:04 GMT

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

Re: Difference between values in two rows

mrinmoynano — Thu, 29 Jun 2017 05:24:08 GMT

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.

Re: Difference between values in two rows

Kurt_Bremser — Thu, 29 Jun 2017 05:38:54 GMT

You missed the set keyword in your code:

data want;
set work.query_for_append_table_0002;
difference = dif(Pyrometer Channel01);
run;

Re: Difference between values in two rows

mrinmoynano — Thu, 29 Jun 2017 06:31:44 GMT

still has some problem. Please check

Re: Difference between values in two rows

Kurt_Bremser — Thu, 29 Jun 2017 06:38:21 GMT

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.

Re: Difference between values in two rows

mrinmoynano — Thu, 29 Jun 2017 08:45:25 GMT

Thank you It is working now