BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mrinmoynano
Fluorite | Level 6

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

12 REPLIES 12
mrinmoynano
Fluorite | Level 6
1
2 1
3 2
4 3
5 4
6 5
6
ballardw
Super User

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.

 

Kurt_Bremser
Super User

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;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Astounding
PROC Star

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

mrinmoynano
Fluorite | Level 6

only on SAS Enterprise guide.... without code


2017-06-29 08_50_58-Solved_ Difference between values in two rows - SAS Support Communities.png
mrinmoynano
Fluorite | Level 6

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

 

 

mrinmoynano
Fluorite | Level 6

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.

mrinmoynano
Fluorite | Level 6

still has some problem. Please check


2017-06-29 11_58_54-Summary Statistics update1 - SAS Enterprise Guide.png
Kurt_Bremser
Super User

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.

mrinmoynano
Fluorite | Level 6

Thank you It is working now

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 40707 views
  • 2 likes
  • 5 in conversation