do loop from observations of a dataset

[leonzheng]

I want to run a loop SAS code, let's say I have a datatable called itemlist, with only 1 column and several observations as below:

Item

001

003

007

025

079

102

and so on... number of observations is not set yet.

I want to run a query where item = (items in itemlist, like 001, 003 ...) one by one.

So how can I build the loop for this?

Tried

%macro item_loop;
%local i item;
%let i=1;
%do %while (%scan(&itemlist, &i) ne );
   %let item = %scan(&itemlist, &i);
   some code // DO query where item = number in dataset one by one and other operations;
   %let i = %eval(&i + 1);
%end;
%mend;
%item_loop;

It seems not work, can anyone help me on correcting my mistake or give me other methods to make the loop work?

Thanks,

[Astounding]

Probably easiest:  CALL EXECUTE.  The basic structure:

data _null_;

call execute('proc sql;');

do until (done);

   set itemlist end=done;

   call execute('specifics of the query for an ITEM go here');

end;

call execute('quit;');

stop;

run;

You haven't provided any details of the query, so it's not really possible to program the interior CALL EXECUTE yet.

[leonzheng]

Thanks, my operation after query is quite complicated, so I am not sure if call execute will work

[Astounding]

Here's an alternative approach that is well-suited for debugging complicated queries.

data _null_;

set itemlist (obs=1);

file 'some text file';

put .............  /* use PUT statements to write out the query to a text file */ ;

run;

Then you get to examine the contents of the text file, adjust the PUT statements until the query looks like it should work properly.  Once it looks correct, get rid of OBS=1 and run with the full list of items.  Then you can execute using:

proc sql;

%include 'some text file';

quit;

[Kurt_Bremser]

---

@leonzheng wrote:
Thanks, my operation after query is quite complicated, so I am not sure if call execute will work

---

It's actually very simple.

Assume your code needs a macro variable &item to work for a single instance.

Wrap it into a macro that handles one instance:

%macro your_mac(item);

/* lots of complicated code here */

%mend;

Then call that macro off of your dataset:

data _null_;
set have;
call execute('%nrstr(%your_mac(' !! strip(item) !! '));');
run;

The single quotes and the %nrstr() prevent premature execution of macro logic when the code is pushed into the execution chain.

[ChrisNZ]

This looks like well-written code, and it should work with the right data.

Try this to see if it makes a difference:

%macro item_loop;
%local i item;
%let i=1;
%do %while (%length(%scan(&itemlist, &i,%str( ))));
   %let item = %scan(&itemlist, &i,%str( ));
   some code // DO query where item = number in dataset one by one and other operations;
   %let i = %eval(&i + 1);
%end;
%mend;
%item_loop;

[leonzheng]

It still not working, maybe my query is not right, should I write
where item = &item.
is this correct?

[ChrisNZ]

We don't know what code you run.

For a string, where ITEM = "&item"  should work.

[leonzheng]

My dataset is numeric, so I still use "where item = &item. " in my query.

The error code is like this:

ERROR: CLI describe error: [IBM][CLI Driver][DB2/AIX64] SQL0007N The statement was not processed
because a character that is not supported in SQL statements was included in the SQL
statement. Invalid character: "&". Text preceding the invalid character: "WHERE
ITEM =". SQLSTATE=42601

[ChrisNZ]

Invalid character: "&".

It looks like you are passing the SQL as a single-quoted string. Macro variables are not recognised there. Use double quotes to have macro language elements evaluated.

[ballardw]

---

@leonzheng wrote:
It still not working, maybe my query is not right, should I write
where item = &item.
is this correct?

---

Is item a character or numeric variable?

If numeric that should work assuming all the &item macro variable values work as numeric values as is.

If character you need quotes just like any other character comparison with a literal string only macro's require the double quotes.

where item= "&item";

if the &item variable contains quotes or leading spaces you might have issues.

Run your code with OPTIONS MPRINT; to see what is actually generated by the macro. Turn off when not needed with OPTION NOMPRINT.

[leonzheng]

The macro actually hasn't done anything yet, from the log, it failed on the first query, error message as I replied above, thx

[Reeza]

Is there an option to use the table itself in a sub query or is the list too long to create a single macro variable with all the values? Adding another macro loop to a complex problem seems like it’s adding complexity.

[Reeza]

SQL subquery:
Select * from x where item in (select item from item_list_table);

Macro variable (if less than 64K characters)
Proc sql noprint;
Select quote(item) into :item_list separated by “, “ from item_list_table;
Quit;

Then use as

Where item in (&item_list.);

[Tom]

Why do you think that will help you?

What is it that you want to do for each value in your list? 

Why can't you just do it for all of the items in your list at the same time?