This blog post teaches you how to import an Excel spreadsheet into the free SAS University Edition, so you can further analyze and graph the data.
First, you need to create a folder on your local computer (such as C:\\SASUniversityEdition\\myfolders\\ ), and then then set that up as a Shared Folder so that Oracle VM VirtualBox can see it. SAS Support has set up a help page describing how to do that. Once you've got the shared folder set up, you can access it via the path /folders/myfolders/ in your SAS job (fyi - the emulator is running a flavor of Unix, therefore this is a Unix path).
Now when you place files (such as Excel spreadsheets) into this folder on your local computer, your SAS jobs (running in the Virtualbox emulator) will be able to see them. So let's download an interesting spreadsheet from the Web, and save it into that location...
The price of gasoline (petrol) here in the US has always been of interest to me. Possibly because I used to have a Ford F250 truck with a 6.8 liter V10 engine that got 8mpg in city driving (I now have a Prius! LOL)
Therefore I'm glad the US Energy Information Administration tracks the average price for a gallon of gasoline in the US, and makes it available on their Web site. Use your favorite browser on your local computer, and go to their page and click the Download Data (XLS File) link, located near the top of the page. Save the spreadsheet on your local computer into C:\\SASUniversityEdition\\myfolders\\EMM_EPMR_PTE_NUS_DPGw.xls
Now you can run the following code in your SAS University Edition to import the spreadsheet data into a SAS dataset:
PROC IMPORT DBMS=xls OUT=eia (rename=(a=date b=price)) replace DATAFILE="/folders/myfolders/EMM_EPMR_PTE_NUS_DPGw.xls"; RANGE="Data 1$A4:B1500"; GETNAMES=NO; RUN;
You can click the dataset in Folders->My Libraries->WORK->EIA and view the table.
But viewing the raw numbers only provides a certain amount of insight. If we could graph the data, then we could much more easily look for trends and such. Luckily you've got SAS University Edition - you can run the following code to create a graph of the data from year 2000 to present. Notice that I'm adding several 'extras' here, to show you the syntax for subsetting the data, adding custom labels to the axes, and formatting the price as US $.
proc sgplot data=eia (where=(date GE '01jan2000'd)); label date='Year' price='US Gasoline Price per Gallon'; series x=date y=price; yaxis valuesformat=dollar10.2; run;
I'm going to let you tell me what you see in the graph (in comments)! Can you identify any major events based on abrupt changes in the graph? Do any trends or cycles jump out at you? What are your theories on gasoline prices? :)