topic Re: DATA not getting populated in SAS Enterprise Guide

DATA not getting populated

sas_it — Fri, 02 Jul 2021 10:33:55 GMT

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;

Re: DATA not getting populated

PaigeMiller — Fri, 02 Jul 2021 10:45:15 GMT

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.

Re: DATA not getting populated

andreas_lds — Fri, 02 Jul 2021 10:54:17 GMT

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/ta-p/352068)

Re: DATA not getting populated

Cynthia_sas — Fri, 02 Jul 2021 23:51:54 GMT

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

Re: DATA not getting populated

Kurt_Bremser — Sat, 03 Jul 2021 04:45:23 GMT

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.