topic Re: Read Data Set through Do Loop. in SAS Programming

Read Data Set through Do Loop.

Chikku — Mon, 15 Jul 2013 03:19:18 GMT

Is that possible to read one by one observation from a data set through Do Loop?

My requirement is to traverse through the observations of the data set using loops.

Thank you:)

Re: Read Data Set through Do Loop.

Ksharp — Mon, 15 Jul 2013 08:41:51 GMT

Not tested code;

data _null_;

do until(last);

set sashelp.class end=last;

n+1;

end;

put n=;

run;

Ksharp

Re: Read Data Set through Do Loop.

Domenico — Mon, 15 Jul 2013 08:43:58 GMT

Hi,

i'm not sure if this is what you're looking for.

%macro loop;

proc sql noprint;

select count(*) into :observations

from sashelp.class;

quit;

%do i=1 %to &observations;

data _null_;

set sashelp.class (firstobs=&i obs=&i);

putlog "Loop: &i do something";

run;

%end;

%mend;

%loop;

Re: Read Data Set through Do Loop.

Scott_Mitchell — Mon, 15 Jul 2013 09:50:53 GMT

I don't understand. SAS reads and processes the observations sequentially in a datastep, so there is no need for a do loop.

Am I missing something here?

Re: Read Data Set through Do Loop.

DBailey — Mon, 15 Jul 2013 13:43:30 GMT

Sometimes you need to complete your homework per the instructions...:)

Sometimes you need to open a dataset and iterate through the observations performing other functions on each row. I don't think you can do this within a data step. You can simulate a cursor within a macro statement:

%Macro M1(dataset);

%local dsid rc now;

%let dsid = %sysfunc(open(dataset));

%do %while (%sysfunc(fetch(&dsid)) = 0); /* outer loop across rows;*/

%end; /*while fetch loop*/

%mend;

You can use getvarn or getvarc to retrieve values the current row of &dsid but you have to wrap them in %sysfunc().

Re: Read Data Set through Do Loop.

Scott_Mitchell — Tue, 16 Jul 2013 02:49:22 GMT

I think you might be right about the homework to be honest.

I use OPEN And CLOSE frequently when I want to identify the number of OBS in a dataset, but I have never used FETCH. I can see the benefits to create a Macro variable, but can you use these to write back to a dataset?

Re: Read Data Set through Do Loop.

Peter_C — Tue, 16 Jul 2013 07:23:23 GMT

Macro functions cannot create or write to a SAS data set. Veryify this by looking in the language documentation at the options available for the open() function

Re: Read Data Set through Do Loop.

Vince28_Statcan — Tue, 16 Jul 2013 12:28:46 GMT

Hi Scott,

As an example as to why this can be useful from personal experience. I've had to do a levenstein (complev) between all words of a "dictionary" and all words in a given column of a gigantic dataset. Building a proc FCMP taking a character array _TEMPORARY_ as a parameter (the dictionary) allowed me to do so in a single data step with very effective processing speed. In order to pass the said dictionary to the proc FCMP, I needed to do such a do loop on the dictionary dataset to load it in an array.

Vincent

Re: Read Data Set through Do Loop.

Scott_Mitchell — Tue, 16 Jul 2013 14:18:19 GMT

Intriguing Vincent. I look forward to having an opportunity to do that in the future.

Re: Read Data Set through Do Loop.

Scott_Mitchell — Tue, 16 Jul 2013 22:14:23 GMT

Hi Peter,

OPEN, CLOSE, and FETCH are not solely MACRO functions. They can also be used within a datastep,as per below:

DATA MYDATA;

/* CREATE VARIABLES FOR ASSIGNMENT */

/*BY CALL SET */

LENGTH NAME $8 SEX $1 AGE WEIGHT HEIGHT 8;

DROP RC DSID;

DSID=OPEN("SASHELP.CLASS","I");

CALL SET (DSID);

DO I=1 TO 10;

RC=FETCHOBS(DSID,I);

OUTPUT;

END;

RUN;

Re: Read Data Set through Do Loop.

Peter_C — Wed, 17 Jul 2013 05:53:01 GMT

Scott

Of course you are correct.

What I should have pointed out is that OPEN() in the context requested provides only the functionality of the the data step environment through %SYSFUNC() and although the SAS/SCL environment provides a similar function which does support UPDATE and (iirc) NEW these open modes were not ported to the base SAS environment along with several other SCL-type functions (way back in SAS release 6.09e days).

Which is a long winded way of saying

Answer your own question

Read the documentation for OPEN() - there is no WRITE

Should the WRITE mode be needed, learn and use SAS/SCL

Re: Read Data Set through Do Loop.

Scott_Mitchell — Wed, 17 Jul 2013 06:07:01 GMT

Thanks Peter. It is nice to hear about the history of these functions. I only started with V8 and although I feel as though I have a decent grip on things, I certainly have a few of those "why is it so" moments.

Thanks again for clearing that up.

Re: Read Data Set through Do Loop.

sam101 — Wed, 11 Jul 2018 15:37:04 GMT

%macro fun;
%do i=1 %to 2;

    data _null_;

        set lib.EMPLOYEE (firstobs=&i obs=&i);

        putlog " I want to print EMP_ID and EMP_NAME value for observation 1 and 2";
        
        
        

    run;

%end;
%mend fun;

%fun;

This is good example ....But how do I print that observation ? For eg. if dataset EMPLOYEE have EMP_ID and EMP_NAME columns then for that observation I want to print EMP_ID and EMP_NAME