SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

SAS Marco Get observation value one by one for Processing

Accepted Solution Solved
Reply
Contributor
Posts: 21
Accepted Solution

SAS Marco Get observation value one by one for Processing

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


Accepted Solutions
Solution
‎09-13-2011 06:51 AM
Super User
Super User
Posts: 7,062

SAS Marco Get observation value one by one for Processing

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;

View solution in original post


All Replies
Solution
‎09-13-2011 06:51 AM
Super User
Super User
Posts: 7,062

SAS Marco Get observation value one by one for Processing

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;

SAS Employee
Posts: 51

SAS Marco Get observation value one by one for Processing

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

Contributor
Posts: 21

SAS Marco Get observation value one by one for Processing

Posted in reply to TimStearn_SASProductManagement_

thanks tim,

i actually doing the similar way u suggest..

liangck

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 259 views
  • 3 likes
  • 3 in conversation