菜鸟请教 -- 如何循环？

shiyiming

有个 Temp 表，其中含有 Office 字段，希望历遍所有记录，并将 office 的值作为变量来传递，调用另一个 Macro
应该怎么做？谢谢各位。

[color=#4040FF:3fsxxt29]Data;
   set in.temp;   
   Do until(EndoFile);
        x = office;
        %region(x);          /*  这行出错。。。。*/
    End;
run;
[/color:3fsxxt29]

shiyiming

from another rookie:
/*
%let dsid=%sysfunc(open(in.temp, is));
%syscall set(dsid);
%let rc = %sysfunc(fetch(&dsid));
%do %while(&rc  EQ 0);
%region(&office);
%let rc = %sysfunc(fetch(&dsid));
%end;
%let rc=%sysfunc(close(&dsid));
*/
or use /*CALL EXECUTE('%region(' || x || ')');*/ to replace your %region(x).

shiyiming

Thanks a lot , Urchin,

But I do not quite understand your codes. 不很明白您的codes.
Little SAS Book 也没有答案。

%let dsid=%sysfunc(open(in.temp, is));
%syscall set(dsid);                                 
%let rc = %sysfunc(fetch(&dsid));        /* 这行是找出 in.temp 的长度吗？*/
%do %while(&rc EQ 0);
%region(&office)                                        /*这行似乎并没有执行，因 Output 窗口没有内容。*/
%let rc = %sysfunc(fetch(&dsid));
%end;                                              /* 这行出错*/
%let rc=%sysfunc(close(&dsid));

插入这行，结果运行不断，只能强行终止。
  CALL EXECUTE('%region('|| x ||')');

shiyiming

#1. I forgot a semicolon after '%region(&amp;office)'. For reference, please read <!-- m --><a class="postlink" href="http://support.sas.com/onlinedoc/913/getDoc/zh/lrdict.hlp/a000127856.htm">http://support.sas.com/onlinedoc/913/ge ... 127856.htm</a><!-- m --> or <!-- m --><a class="postlink" href="http://www2.sas.com/proceedings/forum2007/050-2007.pdf">http://www2.sas.com/proceedings/forum2007/050-2007.pdf</a><!-- m -->

#2. your DATA Step needs a couple of minor changes to avoid endless loop.
/*
Data _NULL_;
Do until(EndoFile);
set in.temp END=EndOFile;
x = office;
CALL EXECUTE('%region(' || x || ')');
End;
run;
*/

#3. the Little SAS book are not written from a programmer's point of view, and its intended audience are PhD's, researchers, but unfortunately does no good and are sometimes even misleading to bottom-level computer slaves.

shiyiming

一个相似的例子。
[code:3l441zzd]data temp;
     input office ;
         num=ceil(ranuni(0)*8);
         cards;
1
2
3
4
5
6
7
8
;
run;
options symbolgen mprint mlogic;
%macro just(x);
       data _null_;
                if &amp;x&gt;=5 then put 'big';
                        else put 'small';
           run;      
%mend;

Data aa;
     set temp;
     if _n_ &gt;=1 then call execute(&quot;%just(&quot;||office||&quot;)&quot;);
run;
[/code:3l441zzd]

shiyiming

谢谢 horse1,

我希望的效果正是，
Office   Size
1          small
2          small
3          small
4          small
5          big
6          big
7          big
8          big

为测试方便，我将您的 Macro 改为

%macro just(x);
      * data _null_;
                data test;
                set temp(keep=office);
                 if &amp;x&gt;=5 then size= 'Big';
                          else size='Small';
      run;      
%mend;

data aa;
     set temp;
     if _n_ &gt;=1 then call execute(&quot;%just(&quot;||office||&quot;)&quot;);
run;

shiyiming

谢谢两位老师指点，我最后对付成这样

%Macro Region(Office);

%Let Office=&amp;Office;                       /*I found I have to add this line here. */

Data in.One_Office;     
    set in.Products;
    If  _Office='&amp;Office' ;                     /* I have to put quotes here.*/
Run;

Proc Freq data=in.One_Office Order = Freq NoPrint;  
    Table Product   / out = in.NewFreq ;
Run;

Proc Sort Data=in.One_Office Out=in.Unique&amp;Office  Nodupkey;     
     By client_no;
Run;

%Mend ;

Data _NULL_;
        Do until(EndoFile);                            /* Not sure if we can us do while loop here. */
                set in.temp END=EndOFile ;
                x = Office;
                call Execute(&quot;%region(&quot;|| x ||&quot;)&quot;);   
        End;
Run;

shiyiming

No &quot;老师&quot;, I'm just nobody.
(1) the %let statement is unnecessary. As a parameter, &amp;office should exist in the local symbol table already. The %let statement only trims the leading and trailing blanks. This can be done with CALL EXECUTE(&quot;%regin(&quot; || left(office) || &quot;)&quot;);
(2) there's no need of subsetting dataset 'in.products' unless the subset is used many times. A WHERE statement or WHERE= option in PROC FREQ / PROC SORT is what people usually do.
(3) use double quotes --- &quot;&amp;office&quot;
(4) either do...while... or do...until... will work as long as your dataset is non-empty (slightly more work to cover the case of empty).
/*
Data _NULL_;
EndoFile=0;
Do while(NOT EndoFile);
set in.temp END=EndOFile ;
call Execute(&quot;%region(&quot;|| office ||&quot;)&quot;);
End;
Run;
*/

shiyiming

You're the man, urchin,

Thanks a lot for your detailed explanation.

However, it does not work if I

1. remove the %let .... line.  
2. change the double quotes to single quotes,

    it became:     [color=#4040FF:kpszkujj]If _office=&quot;|| office ||&quot; [/color:kpszkujj];      instead of     [color=#4040FF:kpszkujj] if  _office='Z';[/color:kpszkujj]

How can I add a where statement in Proc Freq  or Proc Sort.... so that I can save the trouble of creating the in.One_office dataset?

Your  &quot;Do while&quot;  works great !  That's really what I want to know.
In SAS, do we have a similar loop like Scan .......End ?

Thanks again.

shiyiming

(1) my bad, it seems that the CALL EXECUTE routine doesn't like double quotes, so I should have used [u:1ez4pmu5]CALL EXECUTE('%region(' || compress(office) || ')');[/u:1ez4pmu5] instead.
(2) never heard of scan...end... maybe just because I don't know much.