Maybe you are like me and your SAS programs frequently bounce from PROC SQL to data steps depending on the task at hand. Some things you cannot do in PROC SQL as easily as the data step and vice versa. Here’s 3 data set tricks that I have successfully used with PROC SQL. These are some time savers that you can use in your work. However, if you know some others add them in the Comments section – I double-dare you. Trick #1: Drop and Keep Options You can use the DROP and KEEP options on the FROM or CREATE statements. This is handy when you want to exclude or include a list of variables. Consider this example where I saved myself some typing because I know the only variable I don’t want is country. Otherwise I would have had to type out all of the other variables just to exclude a single one. proc sql; create table Newtable as select * from sashelp.prdsal2(drop=country);quit; Trick #2: Limit the Observations OBS allows you to control the number of observations input to the procedure. I would use this if I had a particularly large data table and I just needed to test […]
Tag: Building Business Intelligence
Using SAS to Automatically Forecast
In honor of Easter, here’s a rare egg for the BI Notes blog – information about SAS statistical products! Today guest blogger Phil Low offers some insight about some of the other SAS procedures, particularly those used for the forecasting. Would You Manually Forecast 60,000 Data Items? Ever wake up to find you have to produce forecasts for 60,000 different items? As a data analyst for a medical supply distributor, I have. Manual forecasting under these conditions is neither feasible nor profitable. Enter the FORECAST and ESM procedures. These two procedures combined can produce more than 10 different forecast models to choose from, and can easily be compared against each other with goodness of fit stats. The FORECAST procedure was written in the 1980s and is a simple auto regression with or without trend. Stepwise selection is used to determine which autoregressive lags make it into the final model. Because it is so old, some the more advanced features are better represented by ESM or AUTOREG. The ESM procedure is a moving average model. What’s great about ESM is its ability to throw deterministic trend and seasonality into the mix. You can customize just about every aspect of the model […]
SAS BI Dashboard: User Adoption Is Biggest Challegne
Today I’m blogging at the All Analytics site about how you encourage users to adopt your dashboard (or really any reporting tools). Join the Dashboard E-Chat Tomorrow When I was at the IFSUG conference, I attended a presentation by Stephen Harris, a senior VP at Bank of America. He discussed very thoroughly the issues he had getting users to accept the BI Dashboard as a reporting tool and the methodology he later developed to overcome the issues. There’s an E-Chat with him tomorrow at 3 PM EST where he will be discussing some of the issues and providing advise. It’s a great chance for you to “speak” directly with an industry expert. If you cannot attend the e-chat – just leave a question here (quick site registration required) and then you can check back afterwards for his answer. Facebook and Privacy Plus … check out my All Analytics vblog about the data Facebook shares with other sites when you use the quick log-in features. Please join the conversation – I would love to know what you think. Now I have to go get ready for my red carpet interviews … <ha ha>! Related content: SAS® Global Forum: Is Google […]
MDX: Dynamically Hiding Measures for Compliance
OLAP cubes are great for summarizing data very fast – I love them . In certain environments, HIPAA compliance for personally identifiable data might be a concern. Even at the lowest grain of a cube it might be possible to identify the specific detail data beneath the surface if the summarized number is small enough. For example, if a report shows the number of people that work for a company in a county of a state that are over the age of 50 – it may be beneficial to hide the statistic to protect the identity of that sample size. Using MDX logic, we can dynamically hide measures that meet a certain criteria. In the MDX code below, I have added an additional measure to the OLAP cube I used for SAS Global forum in 2011. It will aggregate the Total Sales measure like normal but if the Total Sales measure is less than 50,000 it will show nothing. DEFINE Member “[SGF2011].[Measures].[Restricted Total Sales]” AS ‘iif([Measures].[Total Sales]<50000,NULL,[Measures].[Total Sales]),FORMAT_STRING = “DOLLAR20.0″‘; The Enterprise Guide screenshot to the right shows the output! Secure the Original Measure with Metadata Permissions One concept I always promote with writing MDX code is […]
Social Network Analysis – SAS on the CrimeWatch
Check out my post on AllAnalytis.Com about how SAS is using analytics to fight fraudsters. Looks out crooks! Plus there was an e-chat yesterday with Angela and Dennis Toomey. [Hint: Read from the bottom.] Lots of cool information about the techno…
SAS Global Forum 2012: Will I See You There?
Check out the results of my SAS Enterprise Guide code – 41 days til SAS Global Forum 2012 in Orlando, Florida! I’m excited – I can’t wait to meet some of my Web friends face-to-face finally! Oh and also catch up with some of my old buddies! Why Should I Attend SAS Global Forum? Here’s the reasons I like to attend: Like Minded PeopleWorking in smaller organizations, I was sometimes was one of a handful of data geeks running loose in the halls. It’s great to meet 3,000+ others who actually know what “code or let SAS EG do it?” means. The SAS users are one of the things that make this a great product to use. There are several meetups planned (think it use to be called Birds of a Feather) – I want to check out the joint SAS-L, SASCommunity, and SAS Discussion meetup. Plus after you arrive there are others – for instance, new SAS users, SAS BI users, etc. Oh and the .. Kick Back Party … you know you want to show off all your groovy dance moves! Don’t worry I’ll have my camera! Exchange of IdeasEach time I attend – I come away with […]
OLAP Cube Studio: Looking at PROC OLAP Code
If you have never worked with OLAP code – then you might be curious how it works and what it looks like. Some developers use SAS OLAP Cube Studio to start their cube building process since it saves some of the typing required. Let’s review some of the code. [Adv Users: check out Steve’s posts about OLAP Code] Exporting PROC OLAP Code There are two methods to export your cube to a SAS program from SAS OLAP Cube Studio. Use Method 1 after you have added custom measurements. Click cube name > Export Code. The Export Code window appears. Use Method 2 after you have completed the Cube Designer wizard. Click the Export Code button. The Export Code window appears. From the Export Code window you can select if you want standard (long) OLAP procedure code or the shorten code. Either way – just add the path and program name. [More at SAS Support about when to use short/long form. ] Under the Hood After opening the BUILD_GA_BLOGSITE.sas program in Enterprise Guide, you see how the OLAP procedure code appears. You can learn to code your own cubes, just by reviewing how the OLAP wizard wrote the code. For instance, […]
Excel and OLAP Viewer: Not enough room for the Cube!
When you use the OLAP Viewer in Microsoft Excel you may have run into the window borders – literally. Your cube and analysis needs more space! Scroll, Scroll, and Scroll to See the Cube Data When you open the OLAP cube in the MS Excel OLAP Viewer the default cube size dictates the area used. You may get 20 rows by 20 columns or you might get 2 rows by 2 columns. So when you want to do some analysis, you expand a dimension and scroll, scroll this way and then scroll, scroll that way to see the coveted data! We need space to do proper analysis!! Oh have the data gods forsaken us?!? Answer to Your OLAP Prayers Obviously I would not bring up this point if I did not have an answer. So here’s how you work yourself out of this tight spot. Trick is to turn on the MS Excel Developer Ribbon. It may already be turned on – for instance, in the figure below you can see the Developer menu between the View and Excellent Analytics menus in my MS Excel menu. If you have the Developer menu – then skip to the next step. Otherwise, […]