How to conditionally check the difference between rows?

Solved
Frequent Contributor
Posts: 122

How to conditionally check the difference between rows?

Hi,

Could you please let me know if there is any way to conditionally check the difference between rows?

For eg:

I have the below dataset(Have), where I need to calculate the shot differences between WHITE and BLACK(race) in corresponding states. i.e calculate the difference between WHITE and BLACK in California then Arizona..etc(dataset NEED below):

Dataset -Have

Race  Shot_rcvd  Area

WHITE 10             CALIFORNIA

WHITE 20             ARIZONA

BLACK 21            CALIFORNIA

BALCK 5              ARIZONA

Dataset Need

Race  Shot_rcvd  Area                     Diff_in_shots

WHITE 10             CALIFORNIA         -11

WHITE 20             ARIZONA               15

BLACK 21            CALIFORNIA           11

BALCK 5              ARIZONA                 -15

Accepted Solutions
Solution
‎04-25-2017 03:28 PM
Posts: 5,529

Re: How to conditionally check the difference between rows?

``````data have;
input RACE :\$12. STATE :\$12. vaccinated_cnt DATE :anydtdte.;
format date yymm7.;
datalines;
WHITE	CALIFORNIA	2	Sep2016
WHITE	ARIZONA	1	Sep2016
WHITE	TEXAS	50	Oct2016
BLACK	CALIFORNIA	1	Sep2016
BLACK	ARIZONA	5	Sep2016
BLACK	TEXAS	10	Oct2016
;

proc sql;
create table cnt_diff as
select
a.state,
a.date,
a.race,
b.race as other_race,
a.vaccinated_cnt - b.vaccinated_cnt as vaccinated_diff
from
have as a inner join
have as b on a.state=b.state and a.date=b.date and a.race ne b.race
order by state, date, race, other_race;
select * from cnt_diff;
quit;
``````
PG

All Replies
Super User
Posts: 13,542

Re: How to conditionally check the difference between rows?

Are there always two white followed by two black with the same two states repeating the same order? Or do you have some of these where there are 3 or more states involved? Any with only one state?
Frequent Contributor
Posts: 122

Re: How to conditionally check the difference between rows?

Hi,

No, it is not going to be the same..there are more states and the numbers of white and black are different too.For some states there will be two black and one white or one black and 4 white or 2 black and 1 white..etc.

Super User
Posts: 13,542

Re: How to conditionally check the difference between rows?

Your example data should include at least one case of each of the types/ combinations of things going on.

If there are dates of events to consider that may be a good idea to include.

If this data is the result of summarizing some data it may be better to post some examples from that data as sometimes raw data makes more sense than summarized for certain forms of manipulation.

Frequent Contributor
Posts: 122

Re: How to conditionally check the difference between rows?

Sure, here is a snapshot of the real data:

 RACE STATE vaccinated_cnt DATE WHITE CALIFORNIA 2 Sep2016 WHITE ARIZONA 1 Sep2016 WHITE TEXAS 50 Oct2016 BLACK CALIFORNIA 1 Sep2016 BLACK ARIZONA 5 Sep2016 BLACK TEXAS 10 Oct2016
Super User
Posts: 23,724

Re: How to conditionally check the difference between rows?

Maybe I'm misunderstanding but a direct comparison doesn't make sense. Wouldn't you need rates to compare? Raw numbers don't provide the correct context.

Frequent Contributor
Posts: 122

Re: How to conditionally check the difference between rows?

Trying to figure out the difference in the number of shots white and blacks received in each state.

Solution
‎04-25-2017 03:28 PM
Posts: 5,529

Re: How to conditionally check the difference between rows?

``````data have;
input RACE :\$12. STATE :\$12. vaccinated_cnt DATE :anydtdte.;
format date yymm7.;
datalines;
WHITE	CALIFORNIA	2	Sep2016
WHITE	ARIZONA	1	Sep2016
WHITE	TEXAS	50	Oct2016
BLACK	CALIFORNIA	1	Sep2016
BLACK	ARIZONA	5	Sep2016
BLACK	TEXAS	10	Oct2016
;

proc sql;
create table cnt_diff as
select
a.state,
a.date,
a.race,
b.race as other_race,
a.vaccinated_cnt - b.vaccinated_cnt as vaccinated_diff
from
have as a inner join
have as b on a.state=b.state and a.date=b.date and a.race ne b.race
order by state, date, race, other_race;
select * from cnt_diff;
quit;
``````
PG
Super User
Posts: 10,778

Re: How to conditionally check the difference between rows?

``````data have;
input  Race \$ Shot_rcvd  Area : \$20.;
cards;
WHITE 10             CALIFORNIA
WHITE 20             ARIZONA
BLACK 21            CALIFORNIA
BLACK 5              ARIZONA
;
run;
data white(index=(area)) black(index=(area));
set have;
if race='WHITE' then output white;
else if race='BLACK' then output black;
run;
data want;
merge white black(keep=shot_rcvd area rename=(shot_rcvd=_shot));
by area;
diff=shot_rcvd-_shot;
output;
diff=-diff;
race='BLACK';
output;
drop _shot;
run;``````
Frequent Contributor
Posts: 122

Re: How to conditionally check the difference between rows?

Thank you all for your suggestions!

☑ This topic is solved.