Help using Base SAS procedures

Creating SAS dataset from Excel file

Frequent Contributor
Posts: 80

Creating SAS dataset from Excel file


I am trying to write code to create a SAS dataset from an Excel file. There are no variable Names in the Excel file. I want to be able to read in the Excel file and create the SAS file and have the system create Variable names, not use the first observation as the variable name.

This is my code:

libname excel "H:\Testing\testfile.xls";
libname temp "c:\Temp";

data temp.new_sas_file;
set excel."Sheet1$"N;

proc print;
title1 "Test file";
libname excel clear;

It creates fine, but the first observation is used as the variable names.
Also, can you tell me what the 'N' stands for in the set statement? I thought maybe it meant NO for get names.

I created it fine using the PROC IMPORT, but wanted another way.

Can someone tell me what I am doing wrong.

Thanks, Nancy
Trusted Advisor
Posts: 2,113

Re: Creating SAS dataset from Excel file

Super User
Posts: 3,106

Re: Creating SAS dataset from Excel file

I suggest you check in SAS help for the options associated with using LIBNAME for reading Excel spreadsheets. The documentation states that if you specify HEADER = NO then SAS will generate column names F1, F2 etc. instead of using the first row.
Frequent Contributor
Posts: 80

Re: Creating SAS dataset from Excel file


I looked up the HEADER=NO and read a SAS paper called "How to Excel with SAS".

This gave me the information on using the LIBNAME statement and Header=no. That worked great.

Still I have this question; is there a way when you have Excel open that you can use the SAS Addin tab (or SAS Solutions tab) and navigate to the file you have in excel and create a SAS dataset from it? My SAS Solutions tab only has the 'Log On' control enabled, but when I click it - it says "A problem has been encountered reading the .ini file".

Thanks so much,

Ask a Question
Discussion stats
  • 3 replies
  • 3 in conversation