As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


Difference between revisions of "Tips:Use PROC SQL to Create SAS Data sets from Excel files"

From sasCommunity
Jump to: navigation, search
m (cat)
(Scheduled June 30, 2014)
Line 22: Line 22:
 
[[Category:Excel]]
 
[[Category:Excel]]
 
[[Category:SQL Procedure]]
 
[[Category:SQL Procedure]]
[[Category:Tip Ready]]
+
[[Category:Tip in Use]]
 
</noinclude>
 
</noinclude>

Revision as of 21:04, 17 June 2014

If you are a beginner like me and have to work with Excel files, this should help you.

Scenario: Suppose you have an Excel file named abc.xlsx that has multiple sheets and let xyz be one of those sheets. Further suppose that within that sheet you want to extract data from the region B4:H200 and create a sas data set. This is how you can accomplish this.

Solution:

PROC SQL;
             CONNECT TO EXCEL (PATH="L:\Raghu\Desktop\Code\abc.xlsx");
                     CREATE TABLE SasDataset AS
                     SELECT * FROM CONNECTION TO EXCEL
                         (SELECT * FROM [xyz$B4:H200]);
             DISCONNECT FROM EXCEL;
QUIT;

Note: The code uses column values from the first row to create variable names.

See TS-792