Hi,
The following macro
%let y=0;
%macro a_test;
%do i=1 %to 5;
%let y=&y,&i;
%end;
%put y=&y;
%mend a_test;
%a_test;
has a output:
y=0,1,2,3,4,5
which is NOT what I want. What I want is the following:
y=0,
1,
2,
3,
4,
5
Can someone please help? Please note, here I have used only 5 items as an example. In my application, there may be tens of thousands of items. Putting them in a macro variable which shows up only in one line would exceed the limit allowed. Thank you so much.
The length of the line (or the presence of line feeds) does not matter in that case, but there is a limit on the number of characters you can stuff into a macro variable. For that specific case I would put the list of values into a table and do a join. For the general case where you want to generate code that is longer than a macro variable can hold there are a couple of solutions.
1) Generate the code instead of a macro variable. Here is an example to illustrate the point.
%macro gen(var,limit);
%local i;
%do i=1 to &limit;
%if &i > 1 then or ;
(&var = &i)
%end;
%mend gen;
proc sql ... ;
....
where %gen(z,10000)
;
....
2) Generate the code to a file and use %INC.
filename query temp;
data _null_;
file query ;
put 'create table TEST_TABLE as'
/' select x'
/' , y'
/' , z'
/' from DATABASE_TABLE'
/' where '
;
do i=1 to 100000;
if i>1 then put @ 6 'or' @ ;
put @ 9 '( z=' i ')';
end;
put ' ;' ;
run;
proc sql ;
%inc query / source2 ;
quit;
Does not look at all like an application that needs or wants macro language.
Can you explain what you actually want to do?
Suppose you want to do the sql:
Proc sql;
create table TEST_TABLE as
select x,
y,
z
from DATABASE_TABLE
where (z=1)
or (z=2)
or ....
or (z=10000);
quit;
Notice that I use a macro variable, say, a_variable to replace (z=1) or (z=2) or ....or (z=10000), but then it's too long. Is this clear?
The length of the line (or the presence of line feeds) does not matter in that case, but there is a limit on the number of characters you can stuff into a macro variable. For that specific case I would put the list of values into a table and do a join. For the general case where you want to generate code that is longer than a macro variable can hold there are a couple of solutions.
1) Generate the code instead of a macro variable. Here is an example to illustrate the point.
%macro gen(var,limit);
%local i;
%do i=1 to &limit;
%if &i > 1 then or ;
(&var = &i)
%end;
%mend gen;
proc sql ... ;
....
where %gen(z,10000)
;
....
2) Generate the code to a file and use %INC.
filename query temp;
data _null_;
file query ;
put 'create table TEST_TABLE as'
/' select x'
/' , y'
/' , z'
/' from DATABASE_TABLE'
/' where '
;
do i=1 to 100000;
if i>1 then put @ 6 'or' @ ;
put @ 9 '( z=' i ')';
end;
put ' ;' ;
run;
proc sql ;
%inc query / source2 ;
quit;
Thank you, Tom.
I haven't worked out the details of your code, but I want to make sure we are on the same page.
For SAS programs, there is a requirement on how many characters you can type on one line (I think it is 6000 characters), which is what I want to get around. So, I don't want a program to look like
proc sql;
...
where (z=1) or (z=2) or ... or (z=10000);
quit;
Instead, I want it to look like
proc sql;
...
where (z=1)
or (z=2)
or ...
or (z=10000);
quit;
In my case, z is actually a variable representing very complicated medical codes or drug codes. Here I write 1,2,..., 10000 just for simplicity.
Thanks, again.
Tom's suggested technique to generate SAS code, writing it to a file and then %include this file for execution will give you full control of how the generated code looks like.
...and I should have read this thread better before re-inventing what Tom already posted... (I'm posting my code now anyway).
What I don't understand: Why do you need to generate such code in first place. Couldn't you just subset your data via an inner join (example at the end of below code)?
data work.names;
input name $8.;
datalines;
Alfred
Carol
Louise
Thomas
;
run;
/* create code and write it to a temporary file */
filename code temp;
data _null_;
set work.names end=last;
/* file print;*/
file code;
if _n_=1 then do;
put
'proc sql;' /
' create table want1 as' /
' select *' /
' from sashelp.class' /
' where name in ('
;
end;
if not last then
do;
put
" '" name +(-1) "',"
;
end;
if last then
do;
put
" '" name +(-1) "')" /
" ;" /
"quit;"
;
end;
run;
/* execute code in temporary file */
options source2;
%include code;
/* select rows in sashelp.class if name matches with row in work.names */
proc sql;
create table want2 as
select l.*
from sashelp.class l, work.names r
where l.name=r.name
;
quit;
Thanks Tom and Patrick, for your help.
Putting the codes first in a file and then including it in the program I have heard sometime ago but never actually implemented. The reason is that I kept thinking there must be a very simple way, ideally by a macro variable combined with a system control character or variable, to create a "newline". Besides, the other parts of my program are quite complicated and I have been trying to avoid introducing additional complexities, sometimes, by sacrificing some generality.
Patrick, inner join is not going to work in my case without essential complications. I am not looking for exact matches, but partial ones. For instance, those (z=1), (z=2), ... are esstially something like (substr(string, n, m) in 'abcde').
Thanks, again.
Lengths of lines of code generated by macro variable expansion or macro execution are not a problem.
For example I frequently use PROC SQL to generate one macro variable from multiple records of data and then use that macro variable in later code.
proc sql noprint ;
%let criteria=0;
select cats('substr(',name,',1,3)=',quote(value))
into :criteria separated by ' or '
from criteria
;
create table new as select * from old where &criteria ;
quit;
The lines of "code' in the source program are nice and short and readable no matter how long the code generated by the macro expansion is. The only problem I see is if you want to put too many characters into one macro variable. Then you need to either split it into multiple variables or use another code generating technique.
i think you can create value from 1-1000 as ur requirement thorugh macro and store in macrovariable as u did in y ie y=0,1,2,3
and when ur writting a query in sql use in operator which yeilds same as want
where (z=1)
or (z=2)
or ...
or (z=10000);
sample code;
proc sql;
select * from table name where z in (&y);
quit;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.