topic Re: %sysfunc(open) where problem in SAS Programming

%sysfunc(open) where problem

forumsguy — Thu, 20 Jun 2013 14:08:46 GMT

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

Re: %sysfunc(open) where problem

Oleg_L — Thu, 20 Jun 2013 14:45:59 GMT

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;

Re: %sysfunc(open) where problem

Vladislaff — Fri, 21 Jun 2013 10:14:01 GMT

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