Looks fine other than the missing PROC SQL statement. You cannot run the CREATE TABLE statement outside of PROC SQL.
%macro goal(code);
proc sql;
CREATE TABLE Append_dataset AS
SELECT Global.*,
FROM SAS.Global
WHERE Global.Code = "&CODE"
;
QUIT;
proc append base=SAS.Global_ok data=Append_dataset force;
run;
%mend ;
But this problem does not look like something that needs macro code.
proc sql;
CREATE TABLE Append_dataset AS
SELECT Global.*
FROM SAS.Global global
WHERE Global.Code in (select code from example)
;
proc append base=SAS.Global_ok data=Append_dataset force;
run;
Or if you did need code generation then put the codes into a single macro variable.
proc sql;
select quote(trim(code),"'")
into :codelist separated by ','
from example
;
CREATE TABLE Append_dataset AS
SELECT Global.*
FROM SAS.Global global
WHERE Global.Code in (&codelist)
;
proc append base=SAS.Global_ok data=Append_dataset force;
run;
If you did want to create a series of macro variables instead (perhaps your list is too long for one macro variable). Then just loop over the list.
%macro goal(dsname);
proc sql noprint;
select quote(trim(code),"'") into :x1- from &dsname;
%let nobs=&sqlobs;
%do index=1 %to &nobs;
proc sql;
CREATE TABLE Append_dataset AS
SELECT Global.*
FROM SAS.Global
WHERE Global.Code = &&x&index
;
QUIT;
proc append base=SAS.Global_ok data=Append_dataset force;
run;
%end;
%mend ;
%goal(example);
Or move the %DO loop
%macro goal(dsname);
proc sql noprint;
select quote(trim(code),"'") into :x1- from &dsname;
%let nobs=&sqlobs;
CREATE TABLE Append_dataset AS
SELECT Global.*
FROM SAS.Global
WHERE Global.Code in (
%do index=1 %to &nobs;
&&x&index
%end;
)
;
QUIT;
proc append base=SAS.Global_ok data=Append_dataset force;
run;
%mend ;
%goal(example);
... View more
