Calculating Percentage Change in Enterprise Guide 7.1

[SunTall]

Dear EG experts,

I am a newbie to SAS Enterprise Guide 7.1 slowly learning my way around it. I have no programming knowledge and use the point and click method to do my work.

 I have been give a  project to bench mark some tuition fees with several competitors and one of the components  to analyse is the percentage change in fees for the various courses from 2019 to 2020 .  The columns in the database that I am interested in 

 looks somewhat like in Table 1 and I want to be able to calculate % change and produce something like Table 2.I have provided screen shots below as an illustration the original data set is much bigger  data set and the other columns are not relevant for what i wish to perform. I have also attached a sample excel file

Would appreciate any advise as to how I can achieve the results in table 2.

For example do I use computed column - advance expression or summary tables  I am really stuck on this as I don't know how to do it.

Table 1 Raw Data

Table 2  (This is the desired outcome required)

Thanks for your help much appreciated

[novinosrin]

Hi @SunTall  It appears the desired output is rather a report than a dataset. @Reeza / @ballardw  can help you with proc report/tabulate.

[SunTall]

Thanks for your advise do I post it there or forward this to them?

[novinosrin]

Please unmark as the question hasn't been answered. I have mentioned Reeza and Ballardw and they would see it. It;s 8:30am in CT(Eastern time) and I am sure Reeza does Central or mountain time. So kindly wait for their solution plz and retain the same thread

[SunTall]

I tried but I can't seem to unmark is there something I am missing sorry this is my first time in this space 

[novinosrin]

Good morning @ShelleySessoms / @ChrisHemedinger  Sorry for the bother early morning. I'm afraid OP has marked the thread as answered by mistake and is unable to un mark. The solution involves great level of Proc report imho and I suggested the OP to wait for expert solutions  most likely from Ballardw/Reeza, of course not discounting any one else's however the two of them are well known. Can you please un-mark for OP so that whomsoever would wanna take a peek into this thread doesn't ignore. Thank you for your help and have a great day!

[ShelleySessoms]

Hello @novinosrin, thank you for alerting us to the mistaken accepted solution. I have unmarked it.

@SunTall: once you receive a solution, please mark that one so that others may be helped by it as @novinosrin notes.

Thanks for using the SAS communities,

Shelley

[SunTall]

Thank you 

[novinosrin]

Hi @SunTall  Until you get a Proc Report solution, you have the following to play with. See if this helps

data have;
infile cards dsd;
input Fees_Year	School_Name	: $15. Course :$20.	Course_Fees :comma10.;
format Course_Fees comma10.;
cards;
2019,AN School,Arts,"109,440"
2019,AN School,Arts,"116,640"
2019,AN School,Arts,"123,840"
2019,AN School,Arts,"169,920"
2019,AN School,Arts,"174,720"
2019,AN School,Creative Arts,"109,440"
2019,AN School,Creative Arts,"123,840"
2019,AN School,Design,"109,440"
2019,James School,Architecure,"114,480"
2019,James School,Arts,"114,480"
2019,James School,Commerce,"114,480"
2019,James School,Creative Arts,"114,480"
2019,James School,Communication,"114,480"
2019,CAN School,Architecure,"97,800"
2019,CAN School,Arts,"81,600"
2019,CAN School,Arts,"84,600"
2019,CAN School,Arts,"94,500"
2019,CAN School,Commerce,"111,200"
2019,CAN School,Design,"77,100"
2019,CAN School,Primary Teaching,"110,000"
2019,CAN School,Secondary Teaching,"110,000"
2019,CAN School,Interior design,"97,800"
2019,CAN School,Communication,"83,100"
2019,CDU School,Arts,"72,360"
2019,CDU School,Primary Teaching,"101,760"
2019,CDU School,Secondary Teaching,"101,760"
2019,CDU School,Education,"76,320"
2019,CQU School,Arts,"83,283"
2019,CQU School,Arts,"84,766"
2019,CQU School,Arts,"89,488"
2019,CQU School,Creative Arts,"83,283"
2019,CQU School,Early Childhood Teaching,"104,991"
2019,CQU School,Primary Teaching,"104,991"
2019,CQU School,Secondary Teaching,"117,911"
2019,CQU School,Communication,"84,766"
2019,CSU School,Arts,"57,600"
2019,CSU School,Communication,"69,600"
2019,CUR School,Architecure,"96,300"
2019,CUR School,Arts,"85,800"
2019,CUR School,Commerce,"120,000"
2019,CUR School,Creative Arts,"85,800"
2019,CUR School,Design,"85,200"
2019,CUR School,Early Childhood Teaching,"121,600"
2019,CUR School,Primary Teaching,"121,600"
2019,CUR School,Secondary Teaching,"119,600"
2019,CUR School,Education,"91,200"
2019,CUR School,Interior design,"128,400"
2019,CUR School,Communication,"85,200"
2019,CUR School,Urban Planning,"118,000"
2019,DEE School,Architecure,"90,600"
2019,DEE School,Arts,"82,200"
2019,DEE School,Arts,"87,000"
2019,DEE School,Arts,"109,600"
2019,DEE School,Commerce,"120,800"
2019,DEE School,Design,"82,200"
2019,DEE School,Design,"92,400"
2019,DEE School,Primary Teaching,"112,000"
2019,DEE School,Communication,"82,200"
2019,DEE School,Communication,"87,000"
2019,DEE School,Communication,"92,400"
2020,Adel School,Architecure,"105,000"
2020,Adel School,Arts,"100,500"
2020,Adel School,Arts,"115,500"
2020,Adel School,Communication,"100,500"
2020,Adel School,Communication,"115,500"
2020,AN School,Arts,"114,912"
2020,AN School,Arts,"122,472"
2020,AN School,Arts,"130,032"
2020,AN School,Arts,"173,376"
2020,AN School,Arts,"178,416"
2020,AN School,Arts,"183,456"
2020,AN School,Creative Arts,"114,912"
2020,AN School,Creative Arts,"130,032"
2020,AN School,Design,"114,912"
2020,James School,Architecure,"120,240"
2020,James School,Arts,"120,240"
2020,James School,Commerce,"120,240"
2020,James School,Creative Arts,"120,240"
2020,James School,Communication,"120,240"
2020,CAN School,Architecure,"100,200"
2020,CAN School,Arts,"87,000"
2020,CAN School,Arts,"96,900"
2020,CAN School,Commerce,"118,000"
2020,CAN School,Design,"80,100"
2020,CAN School,Primary Teaching,"114,000"
2020,CAN School,Secondary Teaching,"114,000"
2020,CAN School,Interior design,"100,200"
2020,CAN School,Communication,"84,000"
2020,CDU School,Architecure,"75,984"
2020,CDU School,Arts,"75,984"
2020,CDU School,Primary Teaching,"106,848"
2020,CDU School,Secondary Teaching,"106,848"
2020,CDU School,Education,"80,136"
2020,CQU School,Arts,"80,640"
2020,CQU School,Arts,"84,960"
2020,CQU School,Arts,"85,680"
2020,CQU School,Creative Arts,"84,060"
2020,CQU School,Creative Arts,"84,960"
2020,CQU School,Early Childhood Teaching,"101,760"
2020,CQU School,Primary Teaching,"101,760"
2020,CQU School,Secondary Teaching,"115,440"
2020,CQU School,Communication,"85,680"
2020,CSU School,Arts,"58,800"
2020,CSU School,Arts,"70,800"
2020,CSU School,Communication,"70,800"
2020,CUR School,Architecure,"99,100"
2020,CUR School,Arts,"88,800"
2020,CUR School,Commerce,"123,800"
2020,CUR School,Creative Arts,"87,000"
2020,CUR School,Creative Arts,"88,800"
2020,CUR School,Design,"88,700"
2020,CUR School,Early Childhood Teaching,"123,500"
2020,CUR School,Primary Teaching,"123,500"
2020,CUR School,Secondary Teaching,"120,500"
2020,CUR School,Education,"92,500"
2020,CUR School,Interior design,"132,300"
2020,CUR School,Communication,"88,600"
2020,CUR School,Communication,"88,700"
2020,CUR School,Urban Planning,"120,700"
2020,DEE School,Architecure,"96,000"
2020,DEE School,Arts,"88,200"
2020,DEE School,Arts,"91,800"
2020,DEE School,Arts,"117,600"
2020,DEE School,Commerce,"128,000"
2020,DEE School,Creative Arts,"96,600"
2020,DEE School,Design,"88,200"
2020,DEE School,Design,"93,000"
2020,DEE School,Design,"96,600"
2020,DEE School,Design,"117,600"
2020,DEE School,Primary Teaching,"117,600"
2020,DEE School,Communication,"88,200"
2020,DEE School,Communication,"91,800"
2020,DEE School,Communication,"96,600"
2020,DEE School,Communication,"117,600"
;



proc summary data=have nway;
class school_name course fees_year;
var course_fees;
output out=temp(drop=_:) mean=;
run;

proc transpose data=temp out=temp2(drop=_:) prefix=AverageFee;
by school_name course;
var course_fees;
id fees_year;
run;

data want;
 set temp2;
 if nmiss(averagefee2020,averagefee2019)=0 then Change_pct=(averagefee2020-averagefee2019)/averagefee2019;
 format change_pct percent10.;
run;

proc print noobs;run;

School_Name	Course	AverageFee2019	AverageFee2020	Change_pct
AN School	Arts	138,912	150,444	8%
AN School	Creative Arts	116,640	122,472	5%
AN School	Design	109,440	114,912	5%

[SunTall]

Thank you so much novinosrin. I will take a look at your solution as I said I am novice and have no programming skills but may be I can substitute my values in your code and produce the same results. I will let you know how I go tomorrow. Its late in my part of the world

[Reeza · Accepted solution]

1. Use a SUMMARY task to get the summary statistics first. Make sure to save this to a SAS data set

2. Use a TRANSPOSE task to get the data flipped so that you have one column for each year

3. Use a QUERY task to create a computed column for year over year percent change

4. Use PROC REPORT to display your report

@novinosrin although you can use PROC REPORT/TABULATE, in my opinion only, for beginners especially, with EG I'll leave it as more steps as they can understand it more easily and modify it themselves if changes are needed. It is possible to do this as a single PROC TABULATE though, but I have to look up the formula for percents every single time 🙂 

[novinosrin]

Thank you @Reeza .I agree and me too being novice in proc report/tabulate I wouldn't know what recommendations would be appropriate. I had a discussion with @ballardw  seeking his advice to get me up to speed on Proc report/tabulate, and subsequently I have gradually begun reading it. Admittedly, I have been ignoring this piece for all this while as I found it very boring however it's apparent the time has come for me to learn it thoroughly having realized all of it cannot be video games alike. Hopefully, you will see some surprises from me with proc report/tabulate soon. Let' see 🙂

[TomKari]

Hi, @novinosrin

I was in the same situation a few years ago, I liked proc report but was afraid of it. Then a contract came along where I had to exactly reproduce a very complex report, proc report was the only way to do it. I'm not going to say I mastered it, but I got a heck of a lot better, and I came out of it with the opinion that proc report is truly an amazing product, and really worth the effort to learn.

I bought Art Carpenter's book, and I can't recommend it highly enough.

Tom

[novinosrin]

Thank you Sir @TomKari  for chiming in and letting know your thoughts and recommendation. I really needed it. I have a paid subscription to Oreily media aka safarionline library and I found that book of Art Carpenter there. That gives me huge relief. Cheers! 

[SunTall]

Thank you so much Reeza this works