topic Re: Keep excel sheet name as a variable in SAS Procedures

Keep excel sheet name as a variable

BchBnz — Thu, 10 Apr 2014 15:40:30 GMT

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.

Re: Keep excel sheet name as a variable

Reeza — Thu, 10 Apr 2014 16:25:13 GMT

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;

Re: Keep excel sheet name as a variable

Reeza — Thu, 10 Apr 2014 16:26:36 GMT

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

Re: Keep excel sheet name as a variable

BchBnz — Thu, 10 Apr 2014 19:49:10 GMT

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.

Re: Keep excel sheet name as a variable

BchBnz — Thu, 10 Apr 2014 19:49:58 GMT

Thanks Reeza,

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

Thank you for your help!

Re: Keep excel sheet name as a variable

art297 — Thu, 10 Apr 2014 20:43:42 GMT

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";

ticker="&&v&I";

I think you would accomplish what you want to do.

Re: Keep excel sheet name as a variable

BchBnz — Thu, 10 Apr 2014 21:04:49 GMT

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

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?

Re: Keep excel sheet name as a variable

Reeza — Thu, 10 Apr 2014 21:13:45 GMT

Post your full code/log

Re: Keep excel sheet name as a variable

art297 — Thu, 10 Apr 2014 22:40:42 GMT

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

Re: Keep excel sheet name as a variable

Tom — Thu, 10 Apr 2014 23:13:22 GMT

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

Re: Keep excel sheet name as a variable

BchBnz — Fri, 11 Apr 2014 00:39:44 GMT

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

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

Re: Keep excel sheet name as a variable

BchBnz — Fri, 11 Apr 2014 00:46:49 GMT

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.

Re: Keep excel sheet name as a variable

Tom — Fri, 11 Apr 2014 00:56:27 GMT

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

Re: Keep excel sheet name as a variable

art297 — Fri, 11 Apr 2014 01:07:15 GMT

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.

Re: Keep excel sheet name as a variable

BchBnz — Fri, 11 Apr 2014 11:58:09 GMT

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

Thanks

Re: Keep excel sheet name as a variable

BchBnz — Fri, 11 Apr 2014 12:03:19 GMT

Hello Tom,

The modification worked perfectly!

Thank you!

Re: Keep excel sheet name as a variable

BchBnz — Fri, 11 Apr 2014 12:11:47 GMT

Thank you all for taking time to answer my question.

Both Arthur's modifications and Tom's code are working perfectly.

I will go ahead and give Tom "correct answer" since it has the full code for other people to use.

Again thanks to everyone for your precious assistance!