%sysfunc(open) where problem

[forumsguy]

Hello,

I have a dataset close to 1 million observations. My task is to read only few observations

data have;

input name $ amt run_flag;

cards;

John 100 1

Peter 200 0

Serge 300 1

Mark 400 0

Jonathan 500 0

;

run;

%macro read_rec (ds);

%let table_id=%sysfunc(open(&ds(where=(run_flag=1)),i));

%let rc=%sysfunc(fetch(&table_id ));

%if &rc ne 0 %then

    %do;

    %put something is wrong ...;

    %end;

%else

    %do;

    %let name_code=%sysfunc(getvarc(&table_id, %sysfunc(varnum(&table_id,name))));

    %put name is &name_code;

    %end;

%let rc1 = %sysfunc(close(&table_id));

%mend;

%read_rec(have);

so I would need my macro to run 2 times for values John and Serge. But t runs only for John. I would have used datastep but this is a modification code so I am stuck to this, also since my original dataset is hug, i must use open and close functions... So my problem is to read one by one all matching conditions.. Please suggest where i am going wrong

[Oleg_L]

You should make a loop. Something like this (untested):

%macro read_rec (ds);

%let j=1;

%let table_id=%sysfunc(open(&ds(where=(run_flag=1)),i));

%let rc=%sysfunc(fetch(&table_id ));

%do %while(&rc=0);

   %let name_code=%sysfunc(getvarc(&table_id, %sysfunc(varnum(&table_id,name))));

    %put name is &name_code;

    %end;

%let rc=%sysfunc(fetch(&table_id ));

%let j=%eval(&j+1);

%end;

%let rc1 = %sysfunc(close(&table_id));

%mend;

[Vladislaff]

%macro read_rec (ds);

%let table_id=%sysfunc(open(&ds(where=(run_flag=1)),i));

%do %while (%sysfunc(fetch(&table_id))=0);

  %let name_code=%sysfunc(getvarc(&table_id, %sysfunc(varnum(&table_id,name))));

  %put name is &name_code;

%end;

%let rc1 = %sysfunc(close(&table_id));

%mend;

%read_rec(have);