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

I want to subtract one record from the other where the years are the same without hard coding the years. I think I'm complicating it more than it has to be so hope someone has an easier solution. 

 

What I have: 

 

Data sample_data;

input Fiscal_Yr$ ValueX Category$ ;

datalines;

FY17  7 A

FY17 21 B

FY18 5 A

FY18 15 B

;

Run;

 

I need an end result that has the difference between category B minus A bsed on fiscal year with a new category C. 

 

 

FY17 14 C

FY18 10 C

 

Any help is appreciated.  

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20

Two approaches: either use RETAIN to remember values between observations, do the math, and logic for output your WANT observation.

Or, use SQL and join the table with itself (use alias for a filtering sub-query where you select A to be joined with B observations).

select a.Fiscal_Yr, b.ValueX - a.ValueX as diff, 'C' as Category
from (select * from sampla_data where Category = 'A') as a
inner join (select * from sampla_data where Category = 'B') as b
on a.Fiscal_Yr = b.Fiscal_Yr
;
Data never sleeps

View solution in original post

8 REPLIES 8
LinusH
Tourmaline | Level 20

Two approaches: either use RETAIN to remember values between observations, do the math, and logic for output your WANT observation.

Or, use SQL and join the table with itself (use alias for a filtering sub-query where you select A to be joined with B observations).

select a.Fiscal_Yr, b.ValueX - a.ValueX as diff, 'C' as Category
from (select * from sampla_data where Category = 'A') as a
inner join (select * from sampla_data where Category = 'B') as b
on a.Fiscal_Yr = b.Fiscal_Yr
;
Data never sleeps
CLE
Obsidian | Level 7 CLE
Obsidian | Level 7

Thank you for the reply. 

ChrisBrooks
Ammonite | Level 13

I've changed your first data step to reflect the variable names I think you intended otherwise this should give you what you want

 

Data sample_data;
input Fiscal_Yr $ ValueX Category $ ;
datalines;
FY17  7 A
FY17 21 B
FY18 5 A
FY18 15 B
;
Run;

proc sort data=sample_data;
	by fiscal_yr category;
run;

data want(drop=pre_val);
	set sample_data;
	by fiscal_yr category;
	pre_val=lag(valuex);
	if last.fiscal_yr then do;
		category="C";
		valuex=valuex-pre_val;
		output;
	end;
run;
CLE
Obsidian | Level 7 CLE
Obsidian | Level 7

Thank you for the reply! 

novinosrin
Tourmaline | Level 20

If the Fiscal_Yr are sets of two, you can skip by group processing:

 

Data sample_data;

input Fiscal_Yr$ ValueX Category$ ;

datalines;

FY17  7 A

FY17 21 B

FY18 5 A

FY18 15 B

;

Run;

 

data want(drop=t);

set sample_data;

t=lag(ValueX);

if mod(_n_,2)=0 then do;

ValueX=ValueX-t;

Category='C';

output;

end;

run;

ChrisBrooks
Ammonite | Level 13

That's true @novinosrin but I'd still be inclined to sort the file "just in case" because you know that if you don't then one day it'll be out of order 🙂

novinosrin
Tourmaline | Level 20

@ChrisBrooks Absolutely right, Can't avoid a sort. I took the sample as it was already sorted. I guess the only way to avoid a sort is proc sql or hashes. Linus gave the SQL solution, you gave the datastep and I had some fun. lol. And, I am not so keen to give a hash solution for this req. 🙂

SAS_inquisitive
Lapis Lazuli | Level 10

Using self merge.

 

Data sample_data;
	input Fiscal_Yr$ ValueX Category$;
	datalines;
FY17  7 A
FY17 21 B
FY18 5 A
FY18 15 B
;
Run;

data tmp;
	merge sample_data sample_data(firstobs=2 rename = (Valuex= _Valuex Category= _Category));
	Valuex = _Valuex - Valuex;
	Category = "C";
run;

data want (drop = _:);
	do _n_ = 1 to n by 2;
		set tmp point = _n_ nobs= n;
		output;
	end;

	stop;
run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 8 replies
  • 1301 views
  • 4 likes
  • 5 in conversation