topic Re: macro create table problem? in SAS Programming

macro create table problem?

Olime — Wed, 14 Dec 2016 09:52:05 GMT

Dear all,

I am new to SAS and I am wondering how come the code below can only create the last table instead of three. Here attached the code below for anyone could give me a helping hand. I have tried many times but it can only be able to create the last table 'countrytbl' instead of all of three tables that are storing inside the dataset.

data dtables;
input tablename $;
datalines;
flightnotbl
customertbl
countrytbl
run;

%MACRO createtbl(tname);
proc sql;
create table _&tname
(field1 char(4),
field2 char(1),
field3 char(3));
quit;
%MEND;

data _null_;
set dtables nobs=last;
call symput('tblname',tablename);
%createtbl(&tblname);
run;

Thank you very much!

Re: macro create table problem?

gamotte — Wed, 14 Dec 2016 10:10:29 GMT

Hi,

Your macro contains a proc sql. Calling it from a data step means executing the proc sql inside the datastep.

Also, when you create a macrovariable with a call symput, you cannot use the macrovariable in the data step that generates it.

So the solution would be to call the macro after the data step.

Re: macro create table problem?

RW9 — Wed, 14 Dec 2016 10:24:45 GMT

Hi,

Yes, you have fallen into the same problems as almost everyone else doing macro programming. Let me explain. Macro code is not executable - on its own is does nothing. All its function is is to create text which then gets passed to the Base SAS compiler which is where code gets executed. It does not interact with datastep, so you can't use it as part of a datastep - it only creates text. I have fixed your code given below, however I question why you are doing this at all. You can simply create all three tables in one step:

data flightnotbl customertbl countrytbl;
  length field1 $4 field2 $1 field3 $2;
  if _n_ < 0 then output;
run;

No need for any of your code. As a tip, learn Base SAS which is the programming language. Forget macro language until you actually have a use for it - 99% of the time things can be done in Base SAS, and only if your developing tools, or some specific function would you ever need to use macro.

Fixed your code - note call execute pushes text out to after datastep ends:

data dtables;
input tablename $;
datalines;
flightnotbl
customertbl
countrytbl
run;
 
%MACRO createtbl(tname);
proc sql;
   create table _&tname
   (field1 char(4),
   field2 char(1),
   field3 char(3));
quit;
%MEND;
 
data _null_;
  set dtables;
  call execute('%createtbl('||strip(tablename)||');');
run;