topic Re: Calculating Percentage Change in Enterprise Guide 7.1 in SAS Enterprise Guide

Calculating Percentage Change in Enterprise Guide 7.1

SunTall — Fri, 22 May 2020 11:44:39 GMT

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

Re: Calculating Percentage Change in Enterprise Guide 7.1

novinosrin — Fri, 22 May 2020 12:15:16 GMT

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

Re: Calculating Percentage Change in Enterprise Guide 7.1

SunTall — Fri, 22 May 2020 12:23:22 GMT

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

Re: Calculating Percentage Change in Enterprise Guide 7.1

novinosrin — Fri, 22 May 2020 12:27:11 GMT

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

Re: Calculating Percentage Change in Enterprise Guide 7.1

SunTall — Fri, 22 May 2020 12:35:49 GMT

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

Re: Calculating Percentage Change in Enterprise Guide 7.1

novinosrin — Fri, 22 May 2020 12:42:56 GMT

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!

Re: Calculating Percentage Change in Enterprise Guide 7.1

ShelleySessoms — Fri, 22 May 2020 13:13:04 GMT

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

Re: Calculating Percentage Change in Enterprise Guide 7.1

SunTall — Fri, 22 May 2020 13:21:24 GMT

Thank you

Re: Calculating Percentage Change in Enterprise Guide 7.1

novinosrin — Fri, 22 May 2020 13:22:23 GMT

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%

Re: Calculating Percentage Change in Enterprise Guide 7.1

SunTall — Fri, 22 May 2020 13:36:10 GMT

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

Re: Calculating Percentage Change in Enterprise Guide 7.1

Reeza — Fri, 22 May 2020 15:32:51 GMT

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 🙂

Re: Calculating Percentage Change in Enterprise Guide 7.1

novinosrin — Fri, 22 May 2020 15:57:10 GMT

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 🙂

Re: Calculating Percentage Change in Enterprise Guide 7.1

ghosh — Fri, 22 May 2020 20:08:32 GMT


/* Cleaned up */

options missing='-';
proc report data=dat.schoolfees nowd;
columns ('Compare Annual Average Fees' 'School Name'n  Course) 
		'Course Fees'n, 'Fees Year'n  ('Y over Y' percent);
define 'School Name'n/group;
define Course /group;
define 'Fees Year'n /across;
define 'Course Fees'n/mean ;
define percent/computed f=percent6.2 '% chg';
Compute percent;
percent=(1-(_C3_/_C4_)); /* Variables for the two years) */
endcomp;
format 'Fees Year'n comma9.0; 
run;

Re: Calculating Percentage Change in Enterprise Guide 7.1

TomKari — Fri, 22 May 2020 18:20:46 GMT

Hi, @SunTall

First of all, try not to include Excel or Word files with your messages. Many people are leery about opening them, and some companies won't permit it. Set your data up as a data step, as @novinosrin has done. Then we can easily use it in our programs to solve your questions.

I agree completely with @Reeza 's solution. In case you didn't realize it, every step that she lists can be done using the Enterprise Guide tasks, there's no need to do any SAS programming.

Please note that this is a fairly complex requirement...anything more complex might result in you having to do some SAS programming, so I suggest you try to use some of the SAS free educational materials to at least dip your toe into the topic.

Some of the comments in this discussion may seem a little confusing. The SAS REPORT procedure is incredibly powerful, but because of that it can be very challenging to learn. Save that for a future "stretch" assignment!

Tom

Re: Calculating Percentage Change in Enterprise Guide 7.1

TomKari — Fri, 22 May 2020 18:24:08 GMT

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

Re: Calculating Percentage Change in Enterprise Guide 7.1

novinosrin — Fri, 22 May 2020 20:25:03 GMT

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!

Re: Calculating Percentage Change in Enterprise Guide 7.1

SunTall — Fri, 22 May 2020 22:57:43 GMT

Thank you @Tom for the advise will keep it in mind for future, the file i provided is not an original. The data set I attached is fictitious with fictitious names and numbers. Wouldn't mind knowing how I can set the data up as a data step though.

Re: Calculating Percentage Change in Enterprise Guide 7.1

SunTall — Sat, 23 May 2020 00:25:19 GMT

Thank you so much Reeza this works

Re: Calculating Percentage Change in Enterprise Guide 7.1

TomKari — Sat, 23 May 2020 14:55:27 GMT

Here are a couple of options.

Tom

https://blogs.sas.com/content/sasdummy/2013/01/21/turn-your-data-set-into-a-data-step-program/

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712