Proc Import variable format

[Batman]

I'm trying to read in some Excel data using the Import Procedure.  DBMS XLSX (rows 31-42) changes the date values to an incorrect SAS date value.    DBMS EXCELCS (rows 43-55) produces the correct values if I have a short range name, however, it bombs if I have long range name (rows 55-62).

31         /*DBMS XLSX reads in data incorrectly*/

32         proc import out=F1

33         datafile="S:\HC\ANDERSONTD\PRIVATE\102446\MISC\DateFormat_Example.XLSX"

34         dbms=XLSX replace;

35         getnames=yes;

36         Range="Short Name$a1:a28";

37         run;

NOTE: The import data set has 27 observations and 1 variables.

NOTE: WORK.F1 data set was successfully created.

NOTE: PROCEDURE IMPORT used (Total process time):

      real time           0.05 seconds

      cpu time            0.04 seconds

38        

39         data _null_;

40              set F1;

41              put EndDate;

42         run;

None Given

None Given

None Given

2                                                          The SAS System                             12:47 Wednesday, June 27, 2018

None Given

None Given

None Given

None Given

None Given

None Given

None Given

None Given

None Given

42735

42735

42735

42735

42277

42735

42277

42277

42277

42277

42277

42277

42277

42277

42277

NOTE: There were 27 observations read from the data set WORK.F1.

NOTE: DATA statement used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

43        

44         /*DBMS EXCELCS reads in data correctly, but has a short name*/

45         proc import out=F2

46         datafile="S:\HC\ANDERSONTD\PRIVATE\102446\MISC\DateFormat_Example.XLSX"

47         dbms=EXCELCS replace;

48         Range="Short Name$a1:a28";

49         run;

NOTE: PROCEDURE IMPORT used (Total process time):

      real time           1.34 seconds

      cpu time            0.23 seconds

NOTE: WORK.F2 data set was successfully created.

NOTE: The data set WORK.F2 has 27 observations and 1 variables.

50        

51         data _null_;

52              set F2;

53              put EndDate;

54         run;

None Given

None Given

None Given

None Given

None Given

None Given

None Given

3                                                          The SAS System                             12:47 Wednesday, June 27, 2018

None Given

None Given

None Given

None Given

None Given

12/31/2016

12/31/2016

12/31/2016

12/31/2016

9/30/2015

12/31/2016

9/30/2015

9/30/2015

9/30/2015

9/30/2015

9/30/2015

9/30/2015

9/30/2015

9/30/2015

9/30/2015

NOTE: There were 27 observations read from the data set WORK.F2.

NOTE: DATA statement used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

55        

56         /*However DBMS EXCELCS does not handle long range names (which I have)*/

57         proc import out=F3

58         datafile="S:\HC\ANDERSONTD\PRIVATE\102446\MISC\DateFormat_Example.XLSX"

59         dbms=EXCELCS replace;

60         Range="This Worksheet Has A Long Name$a1:a28";

61         run;

1                                                          The SAS System                             12:49 Wednesday, June 27, 2018

ERROR 65-58: Name 'This Worksheet Has A Long Name$a1:a28' is too long for a SAS name in this context.

NOTE: PROCEDURE IMPORT used (Total process time):

      real time           1.28 seconds

      cpu time            0.21 seconds

ERROR: Import unsuccessful.  See SAS Log for details.

NOTE: The SAS System stopped processing this step because of errors.

62        

[Kurt_Bremser]

Save to a sensible text-based format and read that with a data step. Excel files are not suitable as a data transfer medium.

[Batman]

Really?  I've been using the Import Procedure to read in Excel files for years.   In any case, when you say "text-based format" do you mean something like a .csv format?

[Kurt_Bremser · Accepted solution]

Just read the many, many threads about Excel file issues here. Compared to the reliability I'm used to with SAS, "working" with Excel is a constant PITA.

And yes, a csv file is the way to go. The data step guarantees consistent results, and it will alert you to unexpected input data.

[ballardw]

---

@Batman wrote:

Really?  I've been using the Import Procedure to read in Excel files for years.   In any case, when you say "text-based format" do you mean something like a .csv format?

---

If you haven't run into an issue with importing Excel files you must have exceeding clean data or never combine data from different files.

Proc Import for Excel file formats only examines something like 20 rows of data to set type and length. If you have data with a blank column for the first 20 or so rows then the variable with be treated as $1 which yields odd results when later in the file you have numeric values with more than 1 digit.

At least with CSV or other delimited text you can tell Proc Import to use the option GUESSINGROWS=MAX; to look at all of the records before guessing variable type, informat and length. Better of course is to use the data step code generated by Proc Import for delimited files to create a read program that you actually control all of the informats, variable names and such.