SAS Tip: Use PROC SQL to Create SAS Data sets from Excel files

SAS_Tipster — Thu, 05 Apr 2018 15:16:52 GMT

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!

proc sql;
	connect to Excel (path="c:\somedirectory\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

Thanks to Mysterious Contributor for sharing this tip.

topic SAS Tip: Use PROC SQL to Create SAS Data sets from Excel files in SAS Tips from the Community

SAS Tip: Use PROC SQL to Create SAS Data sets from Excel files