BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BchBnz
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

16 REPLIES 16
Reeza
Super User

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;

Reeza
Super User

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

BchBnz
Obsidian | Level 7

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.

BchBnz
Obsidian | Level 7

Thanks Reeza,

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

Thank you for your help!

art297
Opal | Level 21

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.


BchBnz
Obsidian | Level 7

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?

Reeza
Super User

Post your full code/log

art297
Opal | Level 21

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))

BchBnz
Obsidian | Level 7

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.

art297
Opal | Level 21

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.

BchBnz
Obsidian | Level 7

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

Thanks

Tom
Super User Tom
Super User

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');

BchBnz
Obsidian | Level 7

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

?

Tom
Super User Tom
Super User

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,"'""$ ")


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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 16 replies
  • 3863 views
  • 6 likes
  • 4 in conversation