Desktop productivity for business analysts and programmers

Difference between values in two rows

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

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: 7,410

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

View solution in original post


All Replies
Occasional Contributor
Posts: 7

Re: Difference between values in two rows

1
2 1
3 2
4 3
5 4
6 5
6
Super User
Posts: 11,114

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: 7,410

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
Super User
Super User
Posts: 7,706

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: 5,361

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


2017-06-29 08_50_58-Solved_ Difference between values in two rows - SAS Support Communities.png
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 
16010
18020
20020
25050

 

 

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: 7,410

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
Occasional Contributor
Posts: 7

Re: Difference between values in two rows

still has some problem. Please check


2017-06-29 11_58_54-Summary Statistics update1 - SAS Enterprise Guide.png
Solution
‎06-29-2017 04:44 AM
Super User
Posts: 7,410

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
Occasional Contributor
Posts: 7

Re: Difference between values in two rows

Thank you It is working now

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 541 views
  • 0 likes
  • 5 in conversation