LIST VARIALES+LOOP?

[Andy891]

Dear Community,

I have the following dataset in SAS (let´s call Example)

Code

abh45d

bno873r

132gbv4

cvb67f3

xw32nu7

722gbv5

ilk89f7

qwe12d4

rhg73k98a

For each code on the dataset I need to run a SAS Program (let´s call goal.sas) which use the variable &code to run.

What I have thought was first define a list of variables

options mprint;

%macro varlist;

data _NULL_;

     if 0 then set WORK.Example nobs=n;

     call symputx('nrows',n);

     stop;

run;

%put nobs=&nrows;

proc sql noprint;

select variables into: varname1-:varname&nrows

from WORK.Example

;

quit;

%do i = 1 %to &nrows;

%global &&varname&i;

%end;

%mend varlist;

%varlist;

And once I have defined the list make a loop for each variable contained in example call goal.sas (take into consideration that goal.sas need to use &code as a parameter)

Many thanks for your help

[Tom]

Just use the normal data step to "loop" over the list.  You can use CALL EXECUTE() or a simple PUT statement to generate the code you want.

It is probably easiest to convert your existing program into a macro that takes a single parameter then the code that you need to generate is just a series of macro calls.

So if you made a macro named GOAL something like this:

%macro goal(code);
... code that uses &CODE to reference the value pass in ...
%mend ;

Then your request is just a simple data step like this:

data _null_;
  set example;
  call execute(cats('%nrstr(%goal)(',code,')'));
run;

PS Your current code for making multiple macro variables is way too complicated.  If you did need to make them you only need the one PROC SQL statement.  Creating a macro to do that is just going to make it harder, not easier.

proc sql noprint;
  select code into :varname1- from example;
%let nrows=&sqlobs;
quit;

And if there is some need to force the macro variables to be defined in the global symbol space (which would only matter if you are creating them in code generated by a macro AND they need to be available after the macro that generated them finishes running) then just use a data step instead.

data _null_;
  if eof then call symputx('nobs',_n_-1);
  set example end=eof;
  call symputx(cats('varname',_n_),code,'g');
run;

[Andy891]

Hi Tom,

Many thanks for your quick reponse.

I try to give more details 

I´ve made this simplified version (the goal program perform different task, I´ve tried to make an example easier) of goal.sas and I put into the macro, the objective is from example dataset use the variables to filter one different dataset and then append to one which contain the data validated.

I understand your approach but I´m doing something wrong defining the macro (I´m not a user of macro language)

%macro goal(code);
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 ;

Could you kindly check what it´s wrong with the syntax?

P.S It´s possible to achieve the same solution using this approach+ another piece of code?

proc sql noprint;
  select code into :varname1- from example;
%let nrows=&sqlobs;
quit;

Thanks for your help!

[Tom]

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