Divide by the same variable

[luvscandy27]

Good morning, 

I have searched the internet and can not find the answer to my question. Any assistance would be greatly appreciated. 

Below please find sample data.

FY	month	Fruit	year	Total
2021	JAN	orange	current_2021	$212,741
2020	JAN	orange	previous_2020	$660,370
2021	FEB	orange	current_2021	$259,731
2020	FEB	orange	previous_2020	$340,820
2021	MAR	orange	current_2021	$416,599
2020	MAR	orange	previous_2020	$600,608
2021	JAN	apple	current_2021	$366,962
2020	JAN	apple	previous_2020	$512,251
2021	FEB	apple	current_2021	$374,792
2020	FEB	apple	previous_2020	$362,118
2021	MAR	apple	current_2021	$803,769
2020	MAR	apple	previous_2020	$744,443
2021	JAN	grapes	current_2021	$1,023,649
2020	JAN	grapes	previous_2020	$529,001
2021	FEB	grapes	current_2021	$755,241
2020	FEB	grapes	previous_2020	$786,307
2021	MAR	grapes	current_2021	$614,506
2020	MAR	grapes	previous_2020	$784,661

What I would like to do is determine the percent change by month for each fruit. 

Change would be calculated as follows (2021/2020)-1. What I would like to end 

up with is :

FY	Fruit		year	Total	Delta
2021	orange	JAN	current_2021	$212,741	-68%
2021	orange	FEB	current_2021	$259,731	-24%
2021	orange	MAR	current_2021	$416,599	-31%
2021	apple	JAN	current_2021	$366,962	-28%
2021	apple	FEB	current_2021	$374,792	3%
2021	apple	MAR	current_2021	$803,769	8%
2021	grapes	JAN	current_2021	$1,023,649	94%
2021	grapes	FEB	current_2021	$755,241	-4%
2021	grapes	MAR	current_2021	$614,506	-22%

Is this possible using SAS?

[PaigeMiller]

Could you please provide the data as SAS data step code, as you did in an earlier thread https://communities.sas.com/t5/SAS-Programming/Processing-multiple-months/td-p/723637 ? Providing data as a screen capture isn't useful to us.

[luvscandy27]

Sure.

data date;
infile datalines delimiter = ',';
input fy 4. month $ fruit $  total 12.;
format total dollar12.0;
datalines;
2021, JAN, orange, 212741
2020, JAN, orange, 660370 
2021, FEB, orange, 259731 
2020, FEB, orange, 340820 
2021, MAR, orange, 416599 
2020, MAR, orange, 600608 
2021, JAN, apple,  366962 
2020, JAN, apple,  512251 
2021, FEB, apple,  374792 
2020, FEB, apple,  362118 
2021, MAR, apple,  803769 
2020, MAR, apple,  744443 
2021, JAN, grapes, 1023649 
2020, JAN, grapes, 529001 
2021, FEB, grapes, 755241 
2020, FEB, grapes, 786307 
2021, MAR, grapes, 614506 
2020, MAR, grapes, 784661 
;
run; 

[sbxkoenk]

data have;
infile datalines delimiter = ',';
input fy 4. month $ fruit $  total 12.;
format total dollar12.0;
datalines;
2021, JAN, orange, 212741
2020, JAN, orange, 660370 
2021, FEB, orange, 259731 
2020, FEB, orange, 340820 
2021, MAR, orange, 416599 
2020, MAR, orange, 600608 
2021, JAN, apple,  366962 
2020, JAN, apple,  512251 
2021, FEB, apple,  374792 
2020, FEB, apple,  362118 
2021, MAR, apple,  803769 
2020, MAR, apple,  744443 
2021, JAN, grapes, 1023649 
2020, JAN, grapes, 529001 
2021, FEB, grapes, 755241 
2020, FEB, grapes, 786307 
2021, MAR, grapes, 614506 
2020, MAR, grapes, 784661 
;
run;

proc sort data=have;
 by fruit month fy;
run;

proc transpose data=have out=have_trp;
 by  fruit month;
 id  fy;
 var total;
run; 

data want;
 set have_trp;
 FY=2021;
 year='current_2021';
 Delta=(_2021/_2020)-1;
 format Delta percent12.2;
run;
/* end of program */

[PaigeMiller · Accepted solution]

proc sql;
    create table want as select 
        a.*,
        (a.total-b.total)/b.total as percent format=percentn6.0
    from date(where=(fy=2021)) as a left join date(where=(fy=2020)) as b
        on a.month=b.month and a.fruit=b.fruit
    order by a.fruit,a.fy,a.month;
quit;

Important side issue: it is almost always a bad idea to use text strings like JAN FEB ... to represent month because they will not sort properly, as is the case above. Better to use numbers, and even better to use actual SAS date values here, these will sort properly. In fact, my personal opinion is that any calendar information should be actual SAS date values or SAS date/time values, and never anything else. And then you can use the proper format to make the date value appear as JAN FEB ...

[sbxkoenk]

Hello @luvscandy27 ,

> Is this possible using SAS?

As the answer is almost always, YES! , a better question would be :

How is this possible using SAS?

🙄

Koen