SAS Marco Get observation value one by one for Processing

[Liangck]

Hi all,

I am cracking my head finding the solution on the web for two day alreadly,still cant figure out any solution on this.Hopefully some will help T_T

My problem senario is i am writing a macro to get the each observation from a dataset.

I have a dataset name "SummaryRoute" and inside provided with only one field or coloum name "Route_Code".

What i am trying to do is i wanted to loop out the Route_Code and pass the Route_Code to another Marco for processing.

I research the way of using array but i found that the array in sas is quite much different to be used in other programming language.

I Paste my code here hopefully to have a solution.

%let iterations=10;
%let VAR_NAME= F:\abcdef\Users\liangck\Sample_Output_data\;
%let ext= .csv;
proc sql noprint;
select Count(Route_Code) into : Tcount 
from tgtlib.Tbl_Route_CodeSummary ;
quit;
%MACRO DO_LIST;
%put &Result;
    Data _NULL_;
     /*----Something wrong here-----*/
     /*I wanted to get Route_Code from Tbl_Route_CodeSummary and pass in to csv macro for processing*/
         %DO  I = 1 %TO &Tcount;
         %csv(tgtlib.Tbl_Route_CodeSummary, "&VAR_NAME&I&ext",&Route_Code);
    %END;
%MEND DO_LIST;
%DO_LIST;
%macro csv(dsn, csvfile,Route_Code);
data _null_;
          set &dsn;
          file &csvfile;
          newvar=compress(Route_Code||","||Route_Code); 
          put newvar;
run;
%mend csv;

Hopefully can found the solution.

Regards:

Liangck

[Tom · Accepted solution]

You do not need to work so hard. 

To your original question you can use CALL EXECUTE or just generate the code to a file an include it.

I find that generating the code as text with a data step is MUCH easier to debug.  You can examine the generated code and look for missing commas or quotes.

%let source=tgtlib.Tbl_Route_CodeSummary;

%let outdir = F:\abcdef\Users\liangck\Sample_Output_data;

%let ext=csv;

filename code temp;

data _null_;

   set &source;

   file temp;

   outfile = quote(cats("&outdir\",_n_,".&ext"));

   put '%csv(dsn=' "&source" ',csvfile=' outfile ',' route_code= ')' ;

run;

%inc code / source2 ;

Of course you can do it all in one datastep without the need of a subroutine macro.  Look into the options on the FILE statement.  You can generate the name of the target file .

data _null_;

  set &source;

  length filename $200;

  filename = cats("&outdir\",_n_,".&ext");
file dummy filevar=filename dsd dlm=',';
put Route_Code Route_code ;
run;

[TimStearn_SASProductManagement_]

The reply above from Tom will certain work and is one way to go.  Another way I've used in the past is to load all the column values into macro variables and then write a macro loop to pass the values:

%macro queryAndLoopOnValues
     proc sql;
          select count(distinct route_code)
          into :routeCodeCnt
          from <table>;
     quit;
     /* Do let statement to left justify number */
     %let routeCodeCnt=&routeCodeCnt;
     proc sql;
          select distinct route_code
          into :routeCode1 - :routeCode&routeCodeCnt;
     quit;
     %do i=1 %to &routeCodeCnt;
          %some_macro(&&routeCode&i);
     %end;
%mend;

Just another way to go.

Thanks,

Tim Stearn

[Liangck]

thanks tim,

i actually doing the similar way u suggest..

liangck