topic Re: Conditional logic in SAS Programming

Conditional logic

SASPhile — Fri, 09 Oct 2009 17:39:51 GMT

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;

Re: Conditional logic

CurtisMack — Fri, 09 Oct 2009 18:10:44 GMT

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

Re: Conditional logic

SASPhile — Fri, 09 Oct 2009 18:19:35 GMT

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

Re: Conditional logic

CurtisMack — Fri, 09 Oct 2009 18:30:13 GMT

You left out the ; at the end of:

219 (select * from [&wksht]);
220 %end

Re: Conditional logic

SASPhile — Fri, 09 Oct 2009 18:33:44 GMT

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

Re: Conditional logic

CurtisMack — Fri, 09 Oct 2009 18:44:59 GMT

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.

Re: Conditional logic

SASPhile — Fri, 09 Oct 2009 18:49:21 GMT

Thanks Curtis.
The same code works for other worksheets.But fails for this one.

Re: Conditional logic

CurtisMack — Fri, 09 Oct 2009 20:52:19 GMT

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

Re: Conditional logic

SASPhile — Mon, 12 Oct 2009 14:15:04 GMT

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.