spaces can be read in from the tabs of the Excel ?

[SASAna]

Hi SAS users,

I have a spread sheet with tabs named with spaces like ( TEST A&B , TEST B&C ..Etc)  , Can spaces be read in ? OR is there any other solution on how to read them.

%macro Excel_Read(sheet);

proc import datafile = "path"
    OUT = &sheet
     DBMS=XLSX
        REPLACE;
    SHEET ="&sheet";
 run;

 proc append  base = all_data  data=&sheet; run;
 
 %mend Excel_Read;

%Excel_Read(TEST A&B );
%Excel_Read(TEST B&C);
%Excel_Read(TEST C&D);
%Excel_Read(TEST E&F);

Thanks,

Ana

[Reeza]

You can either allow your data sets to have spaces using the following:

options validvarname=any;

or you can remove the spaces from the data set name using the compress function, something like:

proc import datafile = "path"
    OUT =  %sysfunc(compress(&sheet))
     DBMS=XLSX
        REPLACE;
    SHEET ="&sheet";
 run;

---

@SASAna wrote:

Hi SAS users,

I have a spread sheet with tabs named with spaces like ( TEST A&B , TEST B&C ..Etc)  , Can spaces be read in ? OR is there any other solution on how to read them.

%macro Excel_Read(sheet);

proc import datafile = "path"
    OUT = &sheet
     DBMS=XLSX
        REPLACE;
    SHEET ="&sheet";
 run;

 proc append  base = all_data  data=&sheet; run;
 
 %mend Excel_Read;

%Excel_Read(TEST A&B );
%Excel_Read(TEST B&C);
%Excel_Read(TEST C&D);
%Excel_Read(TEST E&F);

Thanks,

Ana

---

[RW9]

options validvarname=any;

I have to say, I really don't like that option, nor its default position with SAS.  Its a bit like saying: "we have all these rules on how to structure data correctly, in a programmatic way, but what we will suggest is that forget all that and continue in the Excel way of thinking".  

[SASAna]

Thanks Reeza, but i am getting error while reading in the tabs .

WARNING: Apparent symbolic reference B not resolved for TEST A&B . I tried giving the quotes as well & tried compress & tranward functions as well.

[Reeza]

It's a warning not an error and likely doesn't affect your results. It has to do with the & not the name or spaces. & in SAS means a macro variable so it's trying to resolve a macro variable but it's just the &B. You can try masking it with %STR() or other masking functions if desired.

---

@SASAna wrote:
Thanks Reeza, but i am getting error while reading in the tabs .

WARNING: Apparent symbolic reference B not resolved for TEST A&B . I tried giving the quotes as well & tried compress & tranward functions as well.

---

[Tom]

What happened when you tried it?

You are going to have more trouble with trying to use that string as a SAS dataset name than using it as an Excel Sheetname.

But for  your proposed program there is no need to use that as the dataset name.  You can either just use a temporary name

proc import ... out=tempname ...
proc append .... data=tempname ....
proc delete data=tempname; run;

or modify the macro to have the user pass in a valid SAS dataset name.

%macro Excel_Read(sheet,dsname);
proc import datafile = "path"
    OUT = &dsname
     DBMS=XLSX
        REPLACE;
    SHEET ="&sheet";
 run;
 proc append  base = all_data  data=&dsname; run;
%mend Excel_Read;
%Excel_Read(TEST A&B,test1 );

 You might have some trouble with a value like TEST A&B that looks like it is trying to reference a macro variable named B.  You could try fixing that by requiring that the use pass in an already quoted string instead. Then the user could pass in the sheetname using single quotes and the SAS macro processor will not try to interpret macro triggers inside of the single quotes.

%macro Excel_Read(sheet);
...  SHEET = &sheet;
...
%mend Excel_Read;

%Excel_Read('TEST A&B' );

[RW9]

Its not the spaces which are the problem, its the fact that you have & in there which is a macro trigger.  What you could do is:

%macro excel_read(sheet);

  proc import datafile = "path" out=&sheet. dbms=xlsx replace;
    sheet=&sheet.;
  run;
  proc append  base=all_data  data=&sheet.; 
  run;
 
 %mend excel_read;

%excel_read('TEST A&B');
%excel_read('TEST B&C');
%excel_read('TEST C&D');
%excel_read('TEST E&F');

You will note the single quotes, macro only triggers when using double quotes.  A far simpler method however than this is to utilise libname xlsx:

https://blogs.sas.com/content/sasdummy/2015/05/20/using-libname-xlsx-to-read-and-write-excel-files/

You can then do:

libname xlsx "....xlsx";

This will create a libname to the excel file, and in there you will see all the available ranges.  You can proc copy them all out in one step, or you could copy them one by one.  Note the the SAS name for them differs from the sheetname in that spaces will be replaced by underscores, and & is underscore as well I believe.  You can use the named literal to refer to them:

'TEST A&B'n

However I would strongly advise to not program that way, but to convert to SAS naming and then program using that.