how to give excel sheet names as dataset names

[catherinep178]

Hi All, I am trying to use PROC IMPORT for an xls excel file with multiple sheets. The sheet names vary by xls and I wanted to use the sheet name as the dataset name. The problem is the sheet names contain blanks. I have cobbled together a few different codes but I am still stuck,  Please see my code below. The code runs without error but each time I change the data set name in the  PROC IMPORT to have either &SUF1. to &SUF4. the name just stays the same as the first memname. The Data _Null_ step is supposed to count the number of observations in this variable but its not working for me. Does anyone have any ideas? (PS I'm quite new to SAS and programming in general)

%MACRO import1Day_samples(sheet);
  libname xls excel 'V:\x\x_Data\x\Volume cases.xls';

  data T(KEEP=MEMNAME SUF);
    set sashelp.vtable;
    where libname = 'XLS';
    SUF=compress(memname,'','p');
  run;

  %LET SUF= ;
  DATA _NULL_ ;
    set t ;
    suffix=put(_n_,4.);
    CALL SYMPUT(cats('SUF',suffix), SUF); 
  run;

  *import sheet 1 Whole blood for cases;
  PROC IMPORT OUT= WORK.CASES_SHIP5&SUF2.  DATAFILE= "V:\x\x_Data\x\Volume cases.xls" 
    DBMS=EXCEL REPLACE;
    RANGE="&sheet.$A3:K2700"; 
    GETNAMES=NO;
    MIXED=NO;
    SCANTEXT=YES;
    USEDATE=YES;
    SCANTIME=YES;
  RUN;

%MEND import1Day_samples;
*%import1Day_samples(Blood for DNA); *NOTE NEED TO MAKE &SUF1.;
*%import1Day_samples(Whole blood);*NOTE NEED TO MAKE &SUF2.;
*%import1Day_samples(Plasma); *NOTE NEED TO MAKE &SUF3.;
*%import1Day_samples(Serum); *NOTE NEED TO MAKE &SUF4.;

[RW9]

Yes, this is a common problem.  Excel is a really poor medium for data transfer.  Why, well that simple, if we fix your issue and get these random datasets created, of which the name can change, how are you then going to proceed further with your coding, not knowing the dataset names?  This is just one of the issue you will face.  Technically, as you have 9.4 then you can simply do:

libname tmp excel "<path to file>/<name of file>.xlsx";

proc copy in=tmp out=work;
run;

libname tmp clear;

Libname Excel will have a dataset for each sheet (assuming nothing odd in your workbook).  How will you work with that data though, every single file/cell could be messed up.  

[catherinep178]

---

Hi SuperUser

Thank you very much for your reply,

Yes excel is not ideal for working with, but I had thought it might be easier if I used the PROC IMPORT instead of saving each of the sheets as .csv 

Taking a look at your code, I copied and pasted and ran it, in the libname each of the sheets has $ suffixed to the name and the spaces in sheet names still remain. The Files that I need to import all have different file names but they have the key word 'case' or 'control' somewhere in the filename. Thankfully the sheet names have remained consistent: 'Whole Blood', 'Blood DNA', 'Serum', 'Plasma'. It is the first two sheets which are causing me difficulty as they have the spaces in their name. If these did not contain spaces I would be able to use the macro parameter sheet as the dataset name and this would have worked, so the specific problem might be with the part of code below?

*I ran this code of yours and had the issue of $;
libname xls excel 'V:\x\x\Volume cases.xls';

 proc copy in=xls out=work;
 run;
**********

data t(KEEP=MEMNAME SUF);
    set sashelp.vtable;
    where libname = 'XLS';
    SUF=compress(memname,'','p');
  run;

  %LET SUF= ;
  DATA _NULL_ ;
    set t ;
    suffix=put(_n_,4.);
    CALL SYMPUT(cats('SUF',suffix), SUF); 
  run;

[catherinep178]

[TomKari]

First of all, I like @RW9's    solution better.

Hoever, looking at your code, it looks to me like you've combined two concepts. Your idea of using SUF1, SUF2... is aimed at doing this as a macro loop, that will loop through each spreadsheet. But you don't have any macro looping logic, which is typically implemented %do  %to and %end statements. Instead, your just calling the macro with the spreadsheet names.

Which is it that you're trying to do? The suggestions for the two alternatives are very different.

Tom

[catherinep178]

Hi Tom

Yes that does sound like where I am going wrong, and I may be biting off more than I can chew, but if I add macro looping logic to import each of the sheets, using the SUF1-SUF4 macro variable as the dataset name, I hope this will give me the desired effect. 

Alternatively If I use @RW9 's code I still need to figure out how to stop the $ being included in the names and the spaces in the names. I am happy to use either @RW9 's code or use an amended macro looping logic code wrapped around my own attempt. Any further suggestions are very welcomed

Catherine

[TomKari]

Okay, now it's clear where you're trying to go. You have the right idea in your code, but to be honest, I think what you're trying to do may be a little beyond your coding and troubleshooting skills.

But, given @RW9's code, why not just use it and rename your SAS datasets afterwards? Given the level of coding ability you've shown so far, I don't think you'd have any problem setting up code to rename datasets using PROC DATASETS. You've already created pretty much all of the pieces that you need.

Tom

[catherinep178]

Many thanks Tom, I think I will use @RW9 's code to do this task, and just simply rename the datasets. 

Thank you both for your time

Catherine