topic Re: do loop from observations of a dataset in SAS Programming

do loop from observations of a dataset

leonzheng — Wed, 24 Oct 2018 22:33:00 GMT

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,

Re: do loop from observations of a dataset

Astounding — Wed, 24 Oct 2018 22:46:38 GMT

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.

Re: do loop from observations of a dataset

ChrisNZ — Wed, 24 Oct 2018 22:57:31 GMT

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;

Re: do loop from observations of a dataset

leonzheng — Wed, 24 Oct 2018 23:02:58 GMT

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

Re: do loop from observations of a dataset

leonzheng — Wed, 24 Oct 2018 23:10:06 GMT

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

Re: do loop from observations of a dataset

ChrisNZ — Wed, 24 Oct 2018 23:15:22 GMT

We don't know what code you run.

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

Re: do loop from observations of a dataset

leonzheng — Wed, 24 Oct 2018 23:25:42 GMT

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

Re: do loop from observations of a dataset

ballardw — Wed, 24 Oct 2018 23:32:55 GMT

@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.

Re: do loop from observations of a dataset

leonzheng — Wed, 24 Oct 2018 23:51:25 GMT

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

Re: do loop from observations of a dataset

Reeza — Thu, 25 Oct 2018 00:15:45 GMT

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.

Re: do loop from observations of a dataset

Reeza — Thu, 25 Oct 2018 00:20:45 GMT

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

Re: do loop from observations of a dataset

ChrisNZ — Thu, 25 Oct 2018 01:16:51 GMT

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.

Re: do loop from observations of a dataset

Astounding — Thu, 25 Oct 2018 01:52:00 GMT

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;

Re: do loop from observations of a dataset

Tom — Thu, 25 Oct 2018 04:15:33 GMT

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?

Re: do loop from observations of a dataset

Kurt_Bremser — Thu, 25 Oct 2018 07:30:26 GMT

@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.