topic Re: Importing an excel file to SAS on Demand in New SAS User

Importing an excel file to SAS on Demand

Amiera_i — Thu, 22 Apr 2021 07:33:36 GMT

Hi,

I've been trying to import an excel file to SAS on Demand, which I did. But when I run it, it says "The table "WORK.IMPORT" cannot be opened because it does not contain any columns". There are multiple columns in the excel file I don't know why it's not reading it. I need help with this. I've attached it all below for you to understand what I'm talking about.

this is the log:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

73 /* Generated Code (IMPORT) */

74 /* Source File: PA Database-Class of 2017.xlsx */

75 /* Source Path: /home/u58459983/sasuser.v94 */

76 /* Code generated on: 4/22/21, 3:17 AM */

78 %web_drop_table(WORK.IMPORT);

NOTE: Table WORK.IMPORT has been dropped.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

user cpu time 0.00 seconds

system cpu time 0.01 seconds

memory 189.00k

OS Memory 32900.00k

Timestamp 04/22/2021 07:24:52 AM

Step Count 111 Switch Count 2

Page Faults 0

Page Reclaims 20

Page Swaps 0

Voluntary Context Switches 9

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 0

81 FILENAME REFFILE '/home/u58459983/sasuser.v94/PA Database-Class of 2017.xlsx';

83 PROC IMPORT DATAFILE=REFFILE

84 DBMS=XLSX

85 OUT=WORK.IMPORT;

86 GETNAMES=YES;

87 RUN;

NOTE: The import data set has 0 observations and 0 variables.

NOTE: WORK.IMPORT data set was successfully created.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.00 seconds

user cpu time 0.00 seconds

system cpu time 0.00 seconds

memory 2581.68k

OS Memory 35460.00k

Timestamp 04/22/2021 07:24:52 AM

Step Count 112 Switch Count 2

Page Faults 0

Page Reclaims 557

Page Swaps 0

Voluntary Context Switches 14

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 160

89 PROC CONTENTS DATA=WORK.IMPORT; RUN;

NOTE: PROCEDURE CONTENTS used (Total process time):

real time 0.05 seconds

user cpu time 0.05 seconds

system cpu time 0.00 seconds

memory 2598.18k

OS Memory 33928.00k

Timestamp 04/22/2021 07:24:52 AM

Step Count 113 Switch Count 0

Page Faults 0

Page Reclaims 152

Page Swaps 0

Voluntary Context Switches 2

Involuntary Context Switches 1

Block Input Operations 0

Block Output Operations 16

92 %web_open_table(WORK.IMPORT);

94 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

106

Re: Importing an excel file to SAS on Demand

Kurt_Bremser — Thu, 22 Apr 2021 08:35:41 GMT

First of all, it is NEVER a good idea to put anything that is not a SAS dataset, view or catalog into your SASUSER directory.

So I uploaded your Excel file to a directory called "import" on my ODA account, and ran your code:

FILENAME REFFILE '/home/kurt.bremser/import/PA Database-Class of 2017.xlsx';

PROC IMPORT DATAFILE=REFFILE
DBMS=XLSX
OUT=WORK.IMPORT
replace;
GETNAMES=YES;
RUN;

which was successful, but created lots of messages in the log:

 73         FILENAME REFFILE '/home/kurt.bremser/import/PA Database-Class of 2017.xlsx';
 74         
 75         PROC IMPORT DATAFILE=REFFILE
 76         DBMS=XLSX
 77         OUT=WORK.IMPORT
 78         replace;
 79         GETNAMES=YES;
 80         RUN;
 
 NOTE:    Name Truncated...  Clin Assess I Fnl Pract Grade (P/F) -> Clin Assess I Fnl Pract Grade (P
 NOTE:    Name Truncated...  Clin Assess I Fnl Crse Grade (0-4) -> Clin Assess I Fnl Crse Grade (0-
 NOTE:    Name Truncated...  Evid Based Pract for PA Fnl Crse Grade (0-4) -> Evid Based Pract for PA Fnl Crse
 NOTE:    Name Truncated...  Evid Based Pract for PA/MPH Fnl Crse Grade (0-4) -> Evid Based Pract for PA/MPH Fnl 
 NOTE:    Name Truncated...  Found of Med- Biochem Indiv Exam Avg  -> Found of Med- Biochem Indiv Exam
 NOTE:    Name Truncated...  Found of Med- Lab Medicine Section  -> Found of Med- Lab Medicine Secti
 NOTE:    Name Truncated...  Found of Med- Micro Indiv Exam Avg  -> Found of Med- Micro Indiv Exam A
 NOTE:    Name Truncated...  Found of Med- Path Indiv Exam Avg  -> Found of Med- Path Indiv Exam Av
 NOTE:    Name Truncated...  Clin Assess II Fnl Crse Grade (0-4) -> Clin Assess II Fnl Crse Grade (0
 NOTE:    Name Truncated...  Clin Skills I Fnl Crse Grade (0-4) -> Clin Skills I Fnl Crse Grade (0-
 NOTE:    Name Truncated...  Hlth, Just, Soc I Fnl Crse Grade  -> Hlth, Just, Soc I Fnl Crse Grade
 NOTE:    Duplicate Name Change...  Hlth, Just, Soc I Fnl Crse Grade (0-4) -> Hlth, Just, Soc I Fnl Crse Gra_1
 NOTE:    Name Truncated...  Human Behavior Fnl Crse Grade (0-4) -> Human Behavior Fnl Crse Grade (0
 NOTE:    Name Truncated...  Clin Med - Cardiology Sect Exam Grade  -> Clin Med - Cardiology Sect Exam 
 NOTE:    Name Truncated...  Clin Med - Dermatology Sect Exam Grade  -> Clin Med - Dermatology Sect Exam
 NOTE:    Name Truncated...  Clin Med - Endocrinology Sect Exam Grade  -> Clin Med - Endocrinology Sect Ex
 NOTE:    Name Truncated...  Clin Med - Geriatrics Sect Exam Grade  -> Clin Med - Geriatrics Sect Exam 
 NOTE:    Name Truncated...  Clin Med - GU/Renal Sect Exam Grade  -> Clin Med - GU/Renal Sect Exam Gr
 NOTE:    Name Truncated...  Clin Med - Hematology Sect Exam Grade  -> Clin Med - Hematology Sect Exam 
 NOTE:    Name Truncated...  Clin Med - Infect Disease Sect Exam Grade  -> Clin Med - Infect Disease Sect E
 NOTE:    Name Truncated...  Clin Med - MSK/Rheum Sect Exam Grade  -> Clin Med - MSK/Rheum Sect Exam G
 NOTE:    Name Truncated...  Clin Med - Neurology Sect Exam Grade  -> Clin Med - Neurology Sect Exam G
 NOTE:    Name Truncated...  Clin Med - Pulmonology Sect Exam Grade  -> Clin Med - Pulmonology Sect Exam
 NOTE:    Name Truncated...  Clin Med - Reproductive Hlth Sect Exam Grade  -> Clin Med - Reproductive Hlth Sec
 NOTE:    Name Truncated...  Clin Assess III Fnl Crse Grade (0-4) -> Clin Assess III Fnl Crse Grade (
 NOTE:    Name Truncated...  Clin Specialties - Emerg Med Sect Exam Grade Avg  -> Clin Specialties - Emerg Med Sec
 NOTE:    Name Truncated...  Clin Specialties - Pediatrics Sect Exam Grade Avg  -> Clin Specialties - Pediatrics Se
 NOTE:    Name Truncated...  Clin Specialties - Surgery Sect Exam Grade Avg  -> Clin Specialties - Surgery Sect 
 NOTE:    Name Truncated...  Clin Specialties Fnl Crse Grade  -> Clin Specialties Fnl Crse Grade 
 NOTE:    Duplicate Name Change...  Clin Specialties Fnl Crse Grade (0-4) -> Clin Specialties Fnl Crse Grad_1
 NOTE:    Name Truncated...  Hlth, Just, Soc II Fnl Crse Grade  -> Hlth, Just, Soc II Fnl Crse Grad
 NOTE:    Duplicate Name Change...  Hlth, Just, Soc II Fnl Crse Grade (0-4) -> Hlth, Just, Soc II Fnl Crse Gr_1
 NOTE:    Name Truncated...  Anxiety disorders; Trauma- and stress-related disorders -> Anxiety disorders; Trauma- and s
 NOTE:    Name Truncated...  Depressive disorders; Bipolar and related disorders -> Depressive disorders; Bipolar an
 NOTE:    Name Truncated...  Disruptive, impulse-control and conduct disorders; Neurodevelopmental disorders -> Disruptive, 
       impulse-control and 
 NOTE:    Name Truncated...  Paraphilic disorders; Sexual dysfunctions -> Paraphilic disorders; Sexual dys
 NOTE:    Name Truncated...  Personality disorders; Obsessive-compulsive and related disorders -> Personality disorders; Obsessive
 NOTE:    Name Truncated...  Schizophrenia spectrum and other psychotic disorders -> Schizophrenia spectrum and other
 NOTE:    Name Truncated...  Somatic symptom and related disorders; Nonadherence to medical treatment -> Somatic symptom and related 
       diso
 NOTE:    Name Truncated...  Pre-Operative/Post-Operative Care -> Pre-Operative/Post-Operative Car
 NOTE:    Name Truncated...  OB - Labor and Delivery Complications -> OB - Labor and Delivery Complica
 NOTE:    Name Truncated...  OB - Prenatal Care/Normal Pregnancy -> OB - Prenatal Care/Normal Pregna
 NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with 
       options MSGLEVEL=I.
 NOTE: The import data set has 61 observations and 292 variables.
 NOTE: WORK.IMPORT data set was successfully created.

because the Excel was obviously not created with later processing in mind.

Right-click on your Excel file in SAS Studio and look at the properties. It should report a size of 108.5 KB.

Re: Importing an excel file to SAS on Demand

Amiera_i — Thu, 22 Apr 2021 08:43:56 GMT

What should I do after that?

Re: Importing an excel file to SAS on Demand

Kurt_Bremser — Thu, 22 Apr 2021 08:50:54 GMT

As you can see, I successfully imported your Excel file after uploading it to my ODA account, which should have the same environment as yours. What did the properties of your uploaded Excel file say?

Re: Importing an excel file to SAS on Demand

Amiera_i — Thu, 22 Apr 2021 13:54:09 GMT

It says size: 108.5 Kb

Re: Importing an excel file to SAS on Demand

Amiera_i — Thu, 22 Apr 2021 14:00:07 GMT

It actually just started working. Thank you!

Re: Importing an excel file to SAS on Demand

Kurt_Bremser — Thu, 22 Apr 2021 14:03:01 GMT

I can only repeat that I was able to read the file on my ODA account with the code I posted. If the same code does not work for you, then your environment seems to be sufficiently different from mine to cause this (locale setting?).

If the code does not work for you as it does for me, I suggest you report this to SAS Technical Support.