topic Re: replacing special characters in Excel titles in New SAS User

Importing Excel files with macros, not a valid SAS member name

Machine_Gan — Wed, 24 Oct 2018 12:35:59 GMT

Hello SAS community,

I am having troubles importing excel files from my directory.

I have build a macro that loops through the directory to import the excel files.

However, the titles are inconsistent and some titles are providing a error.

Example of excel files in the directory:
APPLES_10-09-2018_REVENUE.xlxs
APPLES_01_01_2017_REVENUE.xlxs
APPLES_10_01_2017_REVENUE.xlxs
APPLES_05-06-2017_REVENUE.xlxs
APPLES_03-10-2018_(REVENUE).xlxs

In this case the - and ( ) characters are giving me a hard time to import them.
Is it possible to write a code in SAS that can change all - characters of the excel titles to _ and remove ( ) characters ?
How will that code sort of look like?

your expertise is much welcome 🙂

The Log + ERROR STATEMENT:

ERROR: The value 'APPLE_INVOICE_01-07-2018_83274.XLS'n is not a valid SAS member name.

75 /* This macro imports all files of type1 specified in the folder and save them as datasets */

76 %macro import_loop1;

77 %do i=1 %to &Total;

78 proc import datafile= "&filenm./&&File&i"

79 out= work.&&name&i

80 dbms=XLSX

81 replace;

82 Sheet=&type1sheet;

83 getnames=yes;

84 datarow=2;

85 run;

87 %let source= &&name&i;

88 data work.&source;

89 set work.&source;

90 Sourcefile_get = SYMGET('source');

91 Sourcefile = substr(Sourcefile_get, 2,(length (Sourcefile_get)-3));

92 drop Sourcefile_get

93 run;

95 proc datasets;

96 append base=work.appended_&filetype1 data=work.&&name&i force;

97 run;

3 The SAS System 20:56 Wednesday, October 17, 2018

98 %end;

99 %mend import_loop1;

100 %import_loop1

ERROR: The value 'APPLE_INVOICE_01-07-2018_83274.XLS'n is not a valid SAS member name.

Re: replacing special characters in Excel titles

SuryaKiran — Wed, 17 Oct 2018 17:57:39 GMT

You can still import the excel file if the file name have special characters like dashes and parenthesis. Show us your log and the code your using to import the file.

I was able to import the following file name successfully.

proc import datafile="/usr/APPLES_03-10-2018_(REVENUE).xlsx"
	out=test_file
	dbms=xlsx;
	run;

Re: replacing special characters in Excel titles

Machine_Gan — Wed, 17 Oct 2018 19:28:24 GMT

Hi,

Thank you for your reply Surya.
I have updated my post with the error statement and the relevant part of my code.

I still think I need to know how to change the name of excel files in my folder in bulk. this is a usefull skill and probably a solution to my problem.

Re: replacing special characters in Excel titles

SuryaKiran — Wed, 17 Oct 2018 19:48:14 GMT

First most thing you need to consider is, How your macro variables are resolved? set Options SYMBBOLGEN for this and in the log you can see what file is being imported and what was your output dataset created. Also make sure you give the right dbms= for xls and xlsx files (both are not same).

Looking at the log, I bet your macros are giving an invalid name for the SAS dataset names.

For example:

data "APPLES_03-10-2018_(REVENUE).xls"n;
set sashelp.class;
run;

The above code will through you the same error that you got. Check your &&name&i values and see if that is valid SAS Name.

File names are not causing an issue here and if you still wants to rename all of the file names in a folder then SAS is not the right tool for this.

Re: replacing special characters in Excel titles

Reeza — Wed, 17 Oct 2018 22:13:42 GMT

The file names shouldn't be problematic unless you're also trying to name the datasets the same name. Or perhaps there's something in your process that's changing them? It would definitely help if you posted your code.

You can use COMPRESS() to remove specific characters.

@Machine_Gan wrote:

Hello SAS community,

I am having troubles importing excel files from my directory.

I have build a macro that loops through the directory to import the excel files.

However, the titles are inconsistent and some titles are providing a error.

Example of excel files in the directory:
APPLES_10-09-2018_REVENUE.xlxs
APPLES_01_01_2017_REVENUE.xlxs
APPLES_10_01_2017_REVENUE.xlxs
APPLES_05-06-2017_REVENUE.xlxs
APPLES_03-10-2018_(REVENUE).xlxs

In this case the - and ( ) characters are giving me a hard time to import them.
Is it possible to write a code in SAS that can change all - characters of the excel titles to _ and remove ( ) characters ?
How will that code sort of look like?

your expertise is much welcome 🙂

The Log + ERROR STATEMENT:

ERROR: The value 'APPLE_INVOICE_01-07-2018_83274.XLS'n is not a valid SAS member name.

75 /* This macro imports all files of type1 specified in the folder and save them as datasets */

76 %macro import_loop1;

77 %do i=1 %to &Total;

78 proc import datafile= "&filenm./&&File&i"

79 out= work.&&name&i

80 dbms=XLSX

81 replace;

82 Sheet=&type1sheet;

83 getnames=yes;

84 datarow=2;

85 run;

86

87 %let source= &&name&i;

88 data work.&source;

89 set work.&source;

90 Sourcefile_get = SYMGET('source');

91 Sourcefile = substr(Sourcefile_get, 2,(length (Sourcefile_get)-3));

92 drop Sourcefile_get

93 run;

94

95 proc datasets;

96 append base=work.appended_&filetype1 data=work.&&name&i force;

97 run;

3 The SAS System 20:56 Wednesday, October 17, 2018

98 %end;

99 %mend import_loop1;

100 %import_loop1

ERROR: The value 'APPLE_INVOICE_01-07-2018_83274.XLS'n is not a valid SAS member name.

Re: replacing special characters in Excel titles

Machine_Gan — Wed, 24 Oct 2018 12:34:27 GMT

thank you,

I have solved my issue by working around the name. the issue is that &&name&i is not a valid sas name. So I couldn't use it for naming my dataset.
I now use &I as dataset name and have used symget(&&name&i) to add a variable to the dataset to indentify the file origin