topic Reading Spreadsheet data in SAS Enterprise Guide

Reading Spreadsheet data

MathapeloPh — Thu, 17 Nov 2016 10:51:14 GMT

How to I resolve the error below?

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
21
22 GOPTIONS ACCESSIBLE;
23 options validvarname=v7;
24 %let path=\\dpc4-75617d\C$\Users\MathapeloPh\Desktop\Move_9093777\Listing Files\Output_Files_Sent_to_Field;
25
26 libname fieldx xlsx "\\%path\PSUs_Segments_161116.xls";
WARNING: Apparent invocation of macro PATH not resolved.
NOTE: Libref FIELDX was successfully assigned as follows:
Engine: XLSX
Physical Name: \\%path\PSUs_Segments_161116.xls
27
28
29 proc contents data=fieldx._ALL_;
30 run;

WARNING: No matching members in directory.
NOTE: PROCEDURE CONTENTS used (Total process time):
real time 0.03 seconds
cpu time 0.04 seconds

Re: Reading Spreadsheet data

Kurt_Bremser — Thu, 17 Nov 2016 11:26:05 GMT

A macro variable needs to be adressed as &name. %name would call a macro.

Try

libname fieldx xlsx "\\&path.\PSUs_Segments_161116.xls";

Also note the dot after the macro variable; this makes sure that the macro processor stops scanning for further characters when building the macro variable name.

Re: Reading Spreadsheet data

MathapeloPh — Thu, 17 Nov 2016 11:45:35 GMT

Thanks @Kurt_Bremser the libname was successfully assigned. Kindly check the warning below it as I now try to read sheets inside the file:

libname fieldx xlsx "\\&path.\PSUs_Segments_161116.xls";
NOTE: Libref FIELDX was successfully assigned as follows:
Engine: XLSX
Physical Name: \\\\dpc4-75617d\C$\Users\MathapeloPh\Desktop\Move_9093777\Listing
Files\Output_Files_Sent_to_Field\PSUs_Segments_161116.xls
27
28
29 proc contents data=fieldx._ALL_;
30 run;

WARNING: No matching members in directory.

Re: Reading Spreadsheet data

Kurt_Bremser — Thu, 17 Nov 2016 12:12:18 GMT

I guess that happens because proc contents (or the contents statement in the datasets procedure) are meant for SAS datasets.

Try this instead:

proc datasets library=fieldx;
quit;