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

 

 I have attached a sample EXCEL file that consists of 3 columns with all numeric, and a 2nd column is a trouble.

 

proc import datafile = "C:\test.xls" 
out = _null11111_ replace dbms = excel ; 

getnames = no;
range = "sheet$A6:c53"; 
dbdsopts="dbsastype=(F2='numeric')"; 

run;

 

After running as above code, I can find that something wrong with 2nd column.

 

Only 2nd column is imported as integer number. I don't know why.

 

The odd thing is that when I run SAS with opening Excel file, SAS can import all columns correctly.

 

Of course, I know that I can handle this problem by editing Excel file, but I would like to fix it with no editing Excel file.

 

Thanks for reading.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Since you must have access to a copy of Excel to use the DBMS=EXCEL option in PROC IMPORT use it to save the file as an XLSX file and IMPORT the new file using the XLSX engine instead.

1047  proc import file="~/test/test.xlsx" dbms=xlsx out=test replace;
1048  getnames = no;
1049  range = "sheet$A6:c53";
1050  run;

NOTE: The import data set has 48 observations and 3 variables.
NOTE: WORK.TEST data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.13 seconds
      cpu time            0.02 seconds


1051  data _null_;
1052  set test;
1053  if b ne int(b) then do;
1054    put (_n_ _all_) (=);
1055    stop;
1056  end;
1057  run;

_N_=1 A=8701.4623591 B=5.2105 C=21718.04648
NOTE: There were 1 observations read from the data set WORK.TEST.

 

View solution in original post

7 REPLIES 7
Tom
Super User Tom
Super User

I am actually more interested in the fact that PROC IMPORT allows you to use OUT=_NULL_.

How could that possibly work?

choeb
Calcite | Level 5

Sorry, it's my mistake. I just roughly did it. As you said, it donesn't work with _null_ name. So I've corrected it

Tom
Super User Tom
Super User

What is your actual problem?  Why do you think you need to use DBSASTYPE option?

 

SAS can read now Excel files directly without using Microsoft software.

But I could not get SAS to read that XLS file. But if I opened it with Excel and saved it as an XLSX file then SAS can read it.

 

1     proc import file="c:\downloads\test.xls" dbms=xls out=test replace;
2     run;

Memory request error
ERROR: Not enough memory available to allocate storage.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.10 seconds
      cpu time            0.00 seconds


3     proc import file="c:\downloads\test.xlsx" dbms=xlsx out=test replace;
4     run;

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 52 observations and 4 variables.
NOTE: WORK.TEST data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.17 seconds
      cpu time            0.04 seconds


5     proc import file="c:\downloads\test.xlsx" dbms=xlsx out=test replace;
6     getnames = no;
7     range = "sheet$A6:c53";
8     run;

NOTE: The import data set has 48 observations and 3 variables.
NOTE: WORK.TEST data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

image.png

Kurt_Bremser
Super User

It's always the same problem. Excel files (especially the older .xls format) are of no use for data transfer.

Save your Excel data to a delimited text file and read that with a data step. This way you have total control over what gets read into SAS, and attributes like types, formats, lengths.

ballardw
Super User

@choeb wrote:

 

 I have attached a sample EXCEL file that consists of 3 columns with all numeric, and a 2nd column is a trouble.

 

proc import datafile = "C:\test.xls" 
out = _null11111_ replace dbms = excel ; 

getnames = no;
range = "sheet$A6:c53"; 
dbdsopts="dbsastype=(F2='numeric')"; 

run;

 

After running as above code, I can find that something wrong with 2nd column.

 

Only 2nd column is imported as integer number. I don't know why.

 

The odd thing is that when I run SAS with opening Excel file, SAS can import all columns correctly.

 

Of course, I know that I can handle this problem by editing Excel file, but I would like to fix it with no editing Excel file.

 

Thanks for reading.

 


Please describe the actual problem. Is column 2 not supposed to be numeric? Not supposed to be integer or what?

SAS does not have an "integer" data type. SAS does assign a display format, typically BEST12. or similar for numeric values. If there is no decimal component to the value then the BEST format does not show them. If you want to see more decimals, or force decimals then you assign a format that displays the values as needed. The internal values SAS stores do not change until you do something to them explicitly.

 

choeb
Calcite | Level 5
The problem is that column 2 is imported as integer by SAS though this is actually real number with decimals

Column 2 is supposed to be numeric with decimals, but I don't know how to do it



Tom
Super User Tom
Super User

Since you must have access to a copy of Excel to use the DBMS=EXCEL option in PROC IMPORT use it to save the file as an XLSX file and IMPORT the new file using the XLSX engine instead.

1047  proc import file="~/test/test.xlsx" dbms=xlsx out=test replace;
1048  getnames = no;
1049  range = "sheet$A6:c53";
1050  run;

NOTE: The import data set has 48 observations and 3 variables.
NOTE: WORK.TEST data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.13 seconds
      cpu time            0.02 seconds


1051  data _null_;
1052  set test;
1053  if b ne int(b) then do;
1054    put (_n_ _all_) (=);
1055    stop;
1056  end;
1057  run;

_N_=1 A=8701.4623591 B=5.2105 C=21718.04648
NOTE: There were 1 observations read from the data set WORK.TEST.

 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 4099 views
  • 0 likes
  • 4 in conversation