Help using Base SAS procedures

Keep excel sheet name as a variable

Accepted Solution Solved
Reply
Contributor
Posts: 56
Accepted Solution

Keep excel sheet name as a variable

Hello everyone,

I have a question regarding how to keep the excel sheet name as a variable.

Here is the code I used to import multiple excel sheets in SAS

%macro excelbook;

libname D 'F:\phdsas\Book1.xls';

proc sql noprint;

select count (distinct (memname)) INTO :TOT

from sashelp.vtable

where libname='D' ;

SELECT DISTINCT (memname) into :s1-:s%trim(%left(&tot))

from sashelp.vtable

where libname='D';

select distinct(compress(MEMNAME,"'$")) into: v1 - :v%trim(%left(&tot))

from sashelp.vtable

where libname='D';

quit;

libname D clear;

%DO I=4 %TO &tot;

%put &&v&i;

%put &&s&i;

proc import DATAfile= 'F:\phdsas\Book1.xls' out=&&v&I  replace;

   RANGE="&&s&i";

   getnames=NO;

   run;

DATA &&v&I;

SET &&v&I;

CODE="&&v&I";

RUN;

proc append base=all data=&&v&I force;

run;

%END;

%mend;

%excelbook;

I wanted to keep the sheet name as a variable called "ticker".

Thank you in advance for your help.


Accepted Solutions
Solution
‎04-10-2014 07:13 PM
Super User
Super User
Posts: 7,039

Re: Keep excel sheet name as a variable

You already have the value in a macro variable just create a new variable to hold the value.

Note that you are working too hard to pull the information into macro variables, you can create them and count them in one step.

Why does the DO loop start at 4 ???

%macro excelbook(path);

libname d &path ;

proc sql noprint;

  select memname

       , compress(memname,"'$"))

    into :s1-:s9999

       , :v1-:v9999

    from sashelp.vtable

    where libname='D'

  ;

%let tot=&sqlobs;

quit;

libname d clear;

%do i=4 %to &tot;

  %put SHEET=&&s&i CODE=&&v&i;

  proc import datafile=&path out=&&v&i  replace;

    range="&&s&i";

    getnames=no;

  run;

  data &&v&i;

    length sheet code $32 ;

    sheet="&&s&i";

    code="&&v&i";

    set &&v&i;

  run;

  proc append base=all data=&&v&i force;

  run;

%end;

%mend excelbook;

%excelbook('f:\phdsas\book1.xls');

View solution in original post


All Replies
Super User
Posts: 19,772

Re: Keep excel sheet name as a variable

Perhaps rather than import use the libname to import the dataset and the indsname option?

libname D 'F:\phdsas\Book1.xls';

data want;

set d.'sheetname$'n indsname=source;

Sheet_Name=source;

run;

Super User
Posts: 19,772

Re: Keep excel sheet name as a variable

Isn't v1-vn your sheet name stored in code?

Contributor
Posts: 56

Re: Keep excel sheet name as a variable

Yes.

In my excel book, the sheet name is the ticker code. So I was wondering if there were a way for me to retain that as a variable.

Thank you for your help.

Contributor
Posts: 56

Re: Keep excel sheet name as a variable

Thanks Reeza,

I will try that and let you know how it goes.

Thank you for your help!

PROC Star
Posts: 7,468

Re: Keep excel sheet name as a variable

I'm confused.  Reeza pointed out that you already capture sheet name with a variable called code. Thus, if you just changed the line:

CODE="&&v&I";

to

ticker="&&v&I";


I think you would accomplish what you want to do.


Contributor
Posts: 56

Re: Keep excel sheet name as a variable

Hi everyone,


Thank you all for your assistance.

Now it is not working at all.

The code worked just fine when I have the sheet numbered, but when I am using sheet names with tickers, it is not working at all.

Here is the error code it brings back when I run the code

For example sheet "AMZG US EQUITY":

1    AMZG US Equity

                 --

               22

              202

ERROR 22-322: Syntax error, expecting one of the following: ;, (, APPENDVER, APPENDVERSION,

              BASE, CREATE, DATA, FORCE, GETSORT, NEW, NOWARN, OUT.

Is there a problem with the sheet name?

Super User
Posts: 19,772

Re: Keep excel sheet name as a variable

Post your full code/log

PROC Star
Posts: 7,468

Re: Keep excel sheet name as a variable

You have to account for the spaces in your sheet names.  That requires changing one line of your code.  Try:

select distinct(translate(trim(compress(MEMNAME,"'$")),'_',' ')) into: v1 - :v%trim(%left(&tot))

instead of:

select distinct(compress(MEMNAME,"'$")) into: v1 - :v%trim(%left(&tot))

Contributor
Posts: 56

Re: Keep excel sheet name as a variable

Hello Arthur,

I tried changing the compress(NEMNAME ... bit

My full code is now:


%macro excelbook;

libname D 'F:\phdsas\Book1.xls';

proc sql noprint;

select count (distinct (memname)) INTO :TOT

from sashelp.vtable

where libname='D' ;

SELECT DISTINCT (memname) into :s1-:s%trim(%left(&tot))

from sashelp.vtable

where libname='D';

select distinct(translate(trim(compress(MEMNAME,"'$")),'_',' ')) into: v1 - :v%trim(%left(&tot))

from sashelp.vtable

where libname='D';

quit;

libname D clear;

%DO I=4 %TO &tot;

%put &&v&i;

%put &&s&i;

proc import DATAfile= 'F:\phdsas\Book1.xls' out=&&v&I  replace;

   RANGE="&&s&i";

   getnames=NO;

   run;

DATA &&v&I;

SET &&v&I;

TICKER="&&v&I";

RUN;

proc append base=all data=&&v&I force;

run;

%END;

%mend;

%excelbook;

The error message I got back is:

WARNING: Apparent symbolic reference TOT not resolved.

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric

       operand is required. The condition was: &tot

ERROR: The %TO value of the %DO I loop is invalid.

ERROR: The macro EXCELBOOK will stop executing.

PROC Star
Posts: 7,468

Re: Keep excel sheet name as a variable

Does the xls file still exist or did you change its name or extenstion?

The only difference between your and my code is that I included the engine in my libname statement i.e.:

libname D excel 'c:\phdsas\Book1.xls';

However, since it was working for you earlier without declaring the excel engine, I didn't see any reason to include it now.

The only thing I can suggest is to ensure that the excel workbook is still in the specified directory, still has an xls extension, close and start a new SAS session and try running the macro without making any other changes.

Contributor
Posts: 56

Re: Keep excel sheet name as a variable

Oh yes it is working now. I had the excel file open so that's probably why I got an error message.

Thanks

Solution
‎04-10-2014 07:13 PM
Super User
Super User
Posts: 7,039

Re: Keep excel sheet name as a variable

You already have the value in a macro variable just create a new variable to hold the value.

Note that you are working too hard to pull the information into macro variables, you can create them and count them in one step.

Why does the DO loop start at 4 ???

%macro excelbook(path);

libname d &path ;

proc sql noprint;

  select memname

       , compress(memname,"'$"))

    into :s1-:s9999

       , :v1-:v9999

    from sashelp.vtable

    where libname='D'

  ;

%let tot=&sqlobs;

quit;

libname d clear;

%do i=4 %to &tot;

  %put SHEET=&&s&i CODE=&&v&i;

  proc import datafile=&path out=&&v&i  replace;

    range="&&s&i";

    getnames=no;

  run;

  data &&v&i;

    length sheet code $32 ;

    sheet="&&s&i";

    code="&&v&i";

    set &&v&i;

  run;

  proc append base=all data=&&v&i force;

  run;

%end;

%mend excelbook;

%excelbook('f:\phdsas\book1.xls');

Contributor
Posts: 56

Re: Keep excel sheet name as a variable

Hello Tom,

Thanks for your help.

I ran the code you gave me and here is the error message.

For example for sheet DPW US EQUITY:

DPW US Equity

          --

          22

          202

ERROR 22-322: Syntax error, expecting one of the following: ;, (, APPENDVER, APPENDVERSION,

              BASE, CREATE, DATA, FORCE, GETSORT, NEW, NOWARN, OUT.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

The do loop starts from 4 because my data is in sheet 4 onward.

Should I change the

select memname

       , compress(memname,"'$")

    into :s1-:s9999

?

Super User
Super User
Posts: 7,039

Re: Keep excel sheet name as a variable

You are not removing the spaces in the sheetnames when you are generating the dataset names.

You need to either add space to the list of characters to compress out of the sheetnames or convert the spaces to other valid character like an underscore.

compress(memname,"'""$ ")


🔒 This topic is solved and locked.

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

Discussion stats
  • 16 replies
  • 1016 views
  • 6 likes
  • 4 in conversation