BookmarkSubscribeRSS Feed
apolace1
Calcite | Level 5

I'm running a fairly simple macro to import multiple sheets from an excel file into SAS. The problem is that in the DATA STEP the datafile ends up with a reverse slash, so that the DATAFiLE line reads:

DATAFILE= "O:\PROJ_LIB\Insurance\Schedule D Part34/2007_2009 Life SchDpt3.xls"

%MACRO LIFE1;

     %DO i = 1 %to 6;

    PROC IMPORT OUT=schd.LIFE_SchDpt4_2007_2009sheet&i.

           DATAFILE= "O:\PROJ_LIB\Insurance\Schedule D Part34\2007_2009 Life SchDpt3.xls"

            DBMS=XLS REPLACE;

            sheet="Sheet&i.";

            GETNAMES=YES;

RUN;

     %END;

    %MEND;

    %LIFE1;

Does anyone have any idea why I keep getting a forward slash instead of the backslash that I put into my code?

Thanks,

Andy

3 REPLIES 3
art297
Opal | Level 21

I've never encountered that behavior before but, regardless, it should still work correctly.  Does it?

apolace1
Calcite | Level 5

No, I get a SAS error similar to this:

Requested Sheet Name not found on Excel -> C:\Users\g1axp03\Documents\My SAS Files\My SAS

Files/2000_2010LifeSchDpt5.xls

Requested Input File Is Invalid

ERROR: Import unsuccessful.  See SAS Log for details.

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

NOTE: PROCEDURE IMPORT used (Total process time):

      real time           8.31 seconds

      cpu time            4.91 seconds

SAS can't recognize the file, because the forward slash is not in the filename.

I've tried some basic troubleshooting. Resetting SAS, changing the file location and changing the file name.

art297
Opal | Level 21

I have always found that SAS ignores the directionality of the slashes (i.e., works well regardless if one uses back slashes, forward slashes, or a combination of them).

However, in your example the input file was on drive O:, but you indicate that it returned a message regarding drive c: .. and in a typical user path rather than the path you had specified.

Could it be that it simply can't find the file in the path that you specified?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1203 views
  • 1 like
  • 2 in conversation