BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8
How to put a condition for the second select statement:

if the macro wksht has quotes around it then use the select statement :
(select * from [%sysfunc(dequote(%superQ(wksht)))]);

if the macro wksht doesnt have quotes around it then use the select statement:

(select * from [&wksht]);

%macro test;
proc sql;
connect to excel (path="&inpt.\BIO RX 2009 08.xls"
header=no mixed=yes );

create table BioRx as
select * from connection to excel
(select * from [%sysfunc(dequote(%superQ(wksht)))]);
disconnect from excel;
quit;
%mend test;
%test;
8 REPLIES 8
CurtisMack
Fluorite | Level 6
If didn't put together a test environment, so this code hasn't been test:
------------------------------
%macro test;
proc sql;
connect to excel (path="&inpt.\BIO RX 2009 08.xls"
header=no mixed=yes );

create table BioRx as
select * from connection to excel
%if %substr(%superQ(wksht),1,1) = %str(%') %then %do;
(select * from [%sysfunc(dequote(%superQ(wksht)))]);
%end;
%else %do;
(select * from [&wksht]);
%end;
disconnect from excel;
quit;
%mend test;
%test;
--------------------------------------------------
Of course in your example, it looks like you could go ahead and use the dequote in either case and not worry about the contitional logic.

Curtis Message was edited by: CurtisMack
SASPhile
Quartz | Level 8
Wksht is resolved as Sheet1$.Strangely this is not working:
202 proc sql;
203 delete from tabnaes where table_name like '%(2)%' or table_name like '%FEIBA%' or
204 table_name like '%NAMES%';
NOTE: 3 rows were deleted from WORK.TABNAES.

205 select table_name into:wksht from tabnaes;
206 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.04 seconds
cpu time 0.03 seconds


207 %put &wksht;
Sheet1$

208 %macro test;
209 proc sql;
210 connect to excel (path="&inpt.\BIO RX 2009 08.xls"
211 header=no mixed=yes );
212
213 create table BioRx as
214 select * from connection to excel
215 %if %substr(%superQ(wksht),1,1) = %str(%') %then %do;
216 (select * from [%sysfunc(dequote(%superQ(wksht)))]);
217 %end;
218 %else %do;
219 (select * from [&wksht]);
220 %end
221 disconnect from excel;
NOTE: Extraneous information on %END statement ignored.
222 quit;
223 %mend test;
224 %test;

ERROR: Describe: ColumnInfo not available
CurtisMack
Fluorite | Level 6
You left out the ; at the end of:

219 (select * from [&wksht]);
220 %end
SASPhile
Quartz | Level 8
I'm steill getting an error:

242 %macro test;
243 proc sql;
244 connect to excel (path="T:\NovoLand\Biopharm Business Analysis SAS Project
244! (2009)\N7customers\Convert2SAS\BIO RX 2009 08.xls"
245 header=no mixed=yes );
246
247 create table BioRx as
248 select * from connection to excel
249 %if %substr(%superQ(wksht),1,1) = %str(%') %then %do;
250 (select * from [%sysfunc(dequote(%superQ(wksht)))]);
251 %end;
252 %else %do;
253 (select * from [&wksht]);
254 %end;
255 disconnect from excel;
256 quit;
257 %mend test;
258 %test;

ERROR: Describe: ColumnInfo not available
CurtisMack
Fluorite | Level 6
That error has nothing to do with the macro conditional. It is coming from the Excel engine. I created a test environment and the code works for me.
SASPhile
Quartz | Level 8
Thanks Curtis.
The same code works for other worksheets.But fails for this one.
CurtisMack
Fluorite | Level 6
Opps, I spoke a little too soon. My logic was not quite working on the quoted values. The error you were getting is not related however. Here is a correction.
---------------------------------------------
%macro test(wksht);
proc sql;
connect to excel (path="c:\temp\temp.xls"
header=no mixed=yes );

create table BioRx as
select * from connection to excel
%if %qsysfunc(substr(%superQ(wksht),1,1)) = %str(%') %then %do;
(select * from [%sysfunc(dequote(%superQ(wksht)))]);
%end;
%else %do;
(select * from [&wksht]);
%end;
disconnect from excel;
quit;
%mend test;
%test(tmp);
%test('tmp');
SASPhile
Quartz | Level 8
Curtis,
macro wksht recolved to 'Data Template$' (with quotes around it).
when i run the following i get an error:

%macro test;
proc sql;
connect to excel (path="&inpt.\Nationwide Childrens Hospital 2009 08.xls"
header=no mixed=yes );

create table NationwideChildern as
select * from connection to excel
%if %substr(%superQ(wksht),1,1) = %str(%') %then %do;
(select * from [%sysfunc(dequote(%superQ(wksht)))]);
%end;
%else %do;
(select * from [&wksht]);
%end;
disconnect from excel;
quit;
%mend test;
%test;



ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: %substr(%superQ(wksht),1,1) = '
ERROR: The macro TEST will stop executing.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 8 replies
  • 1151 views
  • 0 likes
  • 2 in conversation