BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sas_it
Quartz | Level 8

In the following code, data is not getting populated, only fields are displayed. Pls help.

libname abc "C:\Users\cmp\Desktop\New folder";
data abc.a1;
infile "C:\Users\cmp\Desktop\New folder\Exams_list.xlsx" dsd;
length Student_name $15 Exam_name $30 Exam_date $70 Exam_points 8;
run;

data abc.a2;
set abc.a1;
run;

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

Hi:

  You cannot use the INFILE statement with an Excel file. If you need to see what's inside the Excel file prior to writing your code, I recommend PROC CONTENTS:

libname myxl xlsx "C:\Users\cmp\Desktop\New folder\Exams_list.xlsx";
  
proc contents data=myxl._all_ nods;
run;
 
libname myxl clear;

  If you know that the sheet you want to import is called a1 then something like this should work:

libname myxl xlsx "C:\Users\cmp\Desktop\New folder\Exams_list.xlsx";
   
data work.fromXL;
  set myxl.a1;
run;
 
proc print data=work.fromXL;
run;
  
libname myxl clear;

  But, if you don't know the name of the sheet you need to import, then using PROC CONTENTS first will help.

 

  Also, since you posted this in the Enterprise Guide forum, you might not be able to read from or write to your local C: drive, especially if SAS is on a network server. And the other issue is that to use the XLSX engine, your SAS server will need to have SAS/Access for PC File Formats installed and the level of SAS on the server needs to be SAS 9.4 M2 or higher.

 

  I hope these suggestions help you import your data so you can use it.

Cynthia

View solution in original post

4 REPLIES 4
PaigeMiller
Diamond | Level 26

I don't think you can do an INFILE statement to an Excel file.

 

libname abc xlsx "C:\Users\cmp\Desktop\New folder\exams_list.xlsx";

Then your data set abc.a1 will exist and you can use it.

 

 

--
Paige Miller
andreas_lds
Jade | Level 19

Maxim 2

Read the log.

Everything you need to know about your program is in the log. Interpreting messages and NOTEs is essential in finding errors.

(https://communities.sas.com/t5/SAS-Communities-Library/Maxims-of-Maximally-Efficient-SAS-Programmers...)

Cynthia_sas
SAS Super FREQ

Hi:

  You cannot use the INFILE statement with an Excel file. If you need to see what's inside the Excel file prior to writing your code, I recommend PROC CONTENTS:

libname myxl xlsx "C:\Users\cmp\Desktop\New folder\Exams_list.xlsx";
  
proc contents data=myxl._all_ nods;
run;
 
libname myxl clear;

  If you know that the sheet you want to import is called a1 then something like this should work:

libname myxl xlsx "C:\Users\cmp\Desktop\New folder\Exams_list.xlsx";
   
data work.fromXL;
  set myxl.a1;
run;
 
proc print data=work.fromXL;
run;
  
libname myxl clear;

  But, if you don't know the name of the sheet you need to import, then using PROC CONTENTS first will help.

 

  Also, since you posted this in the Enterprise Guide forum, you might not be able to read from or write to your local C: drive, especially if SAS is on a network server. And the other issue is that to use the XLSX engine, your SAS server will need to have SAS/Access for PC File Formats installed and the level of SAS on the server needs to be SAS 9.4 M2 or higher.

 

  I hope these suggestions help you import your data so you can use it.

Cynthia

Kurt_Bremser
Super User

INFILE is for reading text files, and the actual reading has to be done with an INPUT statement.

For Excel files, you have to either use PROC IMPORT or LIBNAME XLSX.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 458 views
  • 1 like
  • 5 in conversation