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
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;
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;
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
thanks tim,
i actually doing the similar way u suggest..
liangck
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.