subtract values of day2 - day1

[bbprakash]

S.No(num) day(char) hours char) value (num)
101 D-01 025:00 PRE 105
101 D-01 020:00 PRE 110
101 D-01 015:00 PRE 108
101 D-01 010:00 PRE 106
101 D-01 005:00 PRE 105

101 D001 025:00 POST 115
101 D001 020:00 POST 120
101 D001 015:00 POST 128
101 D001 010:00 POST 116
101 D001 005:00 POST 115



Hi,

Could someone can help me in solving this problem please.

I need to subtract D - 01 values from D001 ex: 025:00 PRE subtracted from 025:00 POST of D001 and then followed by each rows of (D001 - D-01)

Kind regards,

Bhanu

 

[krispi]

Hi Bhanu. Not sure your dataset is structured too wonderfully, but this code will work providing your data is EXACTLY as you say - i.e. always having pairs of records where day is D-01 or D001 deprending on PRE- or POST-

data data2; set data1;
	newvar=substr(hours,1,6);
run;

proc sort data = data2;
	by newvar;

data data3; set data2;
	by newvar;
	retain preval postval;
	if first.newvar then do;
		if day='D-01' then preval=value;
		else postval=value;
	end;
	else do;
		if preval=. then preval=value;
		if postval=. then postval=value;
	end;
	if last.newvar then calcvar=postval-preval;
	if last.newvar;
run;

Hope this helps...

Chris

 

[kdt82]

Here's a proc sql solution that has the added advantage of making minimal passes through the data.

data have;
   input No day $ hour $10-20 value;
   cards;
101 D-01 025:00 PRE  105
101 D-01 020:00 PRE  110
101 D-01 015:00 PRE  108
101 D-01 010:00 PRE  106
101 D-01 005:00 PRE  105
101 D001 025:00 POST 115
101 D001 020:00 POST 120
101 D001 015:00 POST 128
101 D001 010:00 POST 116
101 D001 005:00 POST 115
   ;run;

proc sql;
   select *, b.value-a.value as nvalue
   from (select * from have where hour ? 'PRE') as A,
        (select * from have where hour ? 'POST') as B
   where a.no = b.no and substr(a.hour,1,6) = substr(b.hour,1,6);
   quit;

 

[bbprakash]

Hi Guys,

Thank you for your solutions but my question is bit weired now I want to make it clear. Under the colume Value:I want to subtract 1st row from 5th row, 2nd row from 6 th, 3rd from 7th, 4th from 8th. assume that there are no other columns (because hours are not always same).

if you can please.

Bhanu

 

[klaz2002]

What you need to do is get the values that you want to compute on the same record. You can rename the d001 set of values to values2.

What I would do is separate the PRE and POST records. Then, merge them back so that you have the D001 values on the same record as the D-01. Then you can do your math.

My only problem with the data that you posted here is that there is not enough information to properly merge the data back. Perhaps you have some more information how your matching (1-5, 2-6) is done?

Klaz

 

[bbprakash]

I haven't merged any datasets, this data is imported from another database where it is manually entered. This is actual data I need to work on.

Kind regards,

Bhanu

 

[kdt82]

Can you please include an example of how you would like the output dataset to appear?

Also, the example dataset you have given: is this one dataset or two datasets?

 

[shenniko]

Just about to finish work (Yay for half days).

But i think the below is along the right lines of what you want (although its very quick and dirty):-

Basically the difference in values between the Pre and Post time..

101 D-01 025:00 PRE 105
101 D001 025:00 POST 115

Data Format_Have;
	set Have;
	Key_Var = substr(hour,1,6);
	If index(hour, 'PRE') >0 then
		Order = 1;
	else
		Order = 2;
run;

Proc sort Data = Format_Have; By Key_Var Order; run;

Data Calculate_Have;
	set Format_Have;
	By Key_Var Order;

	Retain Origional_Value;

	If (first.Key_Var) then
		do;
			Origional_Value = Value;
		end;
	else
		do;
			Origional_Value =Origional_Value;
		end;
	Calculation = sum(Value - Origional_Value);
run