Write and run SAS programs in your web browser

Difference between row values

Reply
Occasional Contributor
Posts: 9

Difference between row values

[ Edited ]

Hi there!

 

I have a dataset where I want to calculate the difference between row values in order - difference between 1st and 2nd row,  3rd and 4th row. 5th and 6th row ......

 

Configuration       Retail_price            Month

1                           300                          1

1                           270                          12

2                           500                          1

2                           470                          12

3                           350                          6

3                           300                          12 

4                           250                          7

4                           200                          12

  

Basically I want difference in retail prices for each configuration at the beginning and end.

 

Thanks for the help!

Super Contributor
Posts: 490

Re: Difference between row values

data want;
set have;
by Configuration Month;
Difference= dif(Retail_price);
if first.Configuration then Difference=.;
run;
SAS Super FREQ
Posts: 8,740

Re: Difference between row values

[ Edited ]

Hi:

  Depending on whether you want a report or an output dataset, you might also use PROC REPORT. Assuming that your data are in "pairs" of months and you always want to subtract month 12 from the first month, then you could make a copy of the RETAIL_PRICE variable and make month 12 negative. Then your summary line would be the difference. Like this:

report_subtract.png

 

Then, you have a few choices with PROC REPORT to either hide the original RETAIL_PRICE column or not show it at all. Report #2 hides the original column but still shows the - minus sign for month 12. Report #3 removes the - minus sign.

alternate_ways_newprice.png

 

Code is below.

 

cynthia

 

** code;

** Create a new_price variable as negative number for month 12;
** and positive number for month 1;
** only make a few rows so comparison fits in one screen shot; data retail; length configuration $10; infile datalines; input configuration $ retail_price month; if month = 12 then new_price = retail_price * -1; else new_price = retail_price; return; datalines; 1 300 1 1 270 12 2 500 1 2 470 12 ; run; title; footnote; proc report data=retail; title '1) Show all columns including the original retail_price column'; column configuration month new_price retail_price ; define configuration / order; define month / display; define new_price / sum; define retail_price / sum; break after configuration / summarize; compute after configuration; configuration = 'Difference'; line ' '; endcomp; run; proc report data=retail; title '2) Hide Retail_Price Column'; column configuration month new_price retail_price ; define configuration / order; define month / display; define new_price / sum; define retail_price / sum noprint; break after configuration / summarize; compute after configuration; configuration = 'Difference'; line ' '; endcomp; run; proc report data=retail; title '3) Do Not Show negative for new_price'; column configuration month new_price retail_price ; define configuration / order; define month / display; define new_price / sum; define retail_price / sum noprint; compute new_price; new_price.sum = abs(new_price.sum); endcomp; break after configuration / summarize; compute after configuration; configuration = 'Difference'; line ' '; endcomp; run;

 

Ask a Question
Discussion stats
  • 2 replies
  • 319 views
  • 1 like
  • 3 in conversation