Help using Base SAS procedures

Proc Import variable format

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

Proc Import variable format

 

 

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        


Accepted Solutions
Solution
3 weeks ago
Super User
Posts: 10,211

Re: Proc Import variable format

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 10,211

Re: Proc Import variable format

[ Edited ]

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 25

Re: Proc Import variable format

Posted in reply to KurtBremser

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?

Solution
3 weeks ago
Super User
Posts: 10,211

Re: Proc Import variable format

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 13,508

Re: Proc Import variable format


@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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 118 views
  • 0 likes
  • 3 in conversation