Use the set operator UNION in PROC SQL SQL syntax contains a few set operators, such as UNION, EXCEPT and INTERSECT. The UNION operator concatenates the results of multiple SQL queries vertically into a single table for all matching rows, which I found particularly useful in PROC SQL while using SAS to manage data. Here come two examples. Example 1 – Transpose data PROC SQL can transform a dataset to any desired structure, without referring to DATA step or PROC TRANSPOSE. For example, SASHELP.CLASS can be transposed from wide to long by the UNION ALL clause, and reversely from long to wide by the MAX function and the ...
Transform a SAS data set to an Excel pivot table by VBA Pivot Table in Excel is the popular data report format, which is similar to an OLAP cube that aggregates data at any dimensions. To create a pivot table for a table with lots of columns, it usually takes 100+ drags and clicks to get job done, which is somehow annoying. I didn't try the SAS’s Add-in for Microsoft Office or SAS Enterprise Guide yet. However, an easy solution to transform a SAS data set toward an Excel pivot table is possibly to use some VBA scripts under Excel. For example, SASHELP.PRDSAL2, which is a free data set shipped with SAS, ...
10 keywords taken out from SAS Global Forum 2012 1. In-memorySAS is famous for hitting hard disk at every operation, which is a proved strategy to save memory.  To speed up the processing of ‘Big Data’, SAS at the server side will aggregate memories, load data into memory and then deal with data there, which is 1000 times faster than the hard disk based operation.2. HadoopInformationweek described that Dr. Goodnight, CEO of SAS, loathes Hadoop, the distributed open source platform. However, this time SAS presented its DI Studio and SAS/ACCESS interface, which now allows data access by Hive and Pig. It looks like a challenge for SAS to run its ...
Stored Processes: SAS's voice on Business Intelligence Everyday I write SAS scripts to extract, transform and load data from various sources, which is a step before the database, and also pull out data to do analysis such as aggregation and regression in SAS, which is a step after the database. According to Norman Nie, a data shop has a four-layer structure: ETL layer, database layer, analytics layer and BI layer. It seems that recently the database layer and the analytics layer look more and more identical. The relational databases start to fiercely create their statistics arms, such as the SQL Server Analysis Services and Oracle R Enterprise. ...
Using SQL for data management Every weekday I pull out data from IBM DB2 database and do analysis in SAS. I use SQLite to record my personal chores, such as credit card transactions. And I am taking courses of database development by Microsoft SQL Server. Then I have pretty enough time to compare the 4 softwares. Recently I have the rough feeling -- the relational database end and the analytics end look more and more similar, while the databases brings more analysis procedures and every statically packages is eager to introduce SQL feature as enhancement. Thus, Structured Query Language (SQL) tends to be the universal ...