topic Re: Output to multiple lines in SAS Procedures

Output to multiple lines

rkong13 — Thu, 04 Apr 2013 22:58:58 GMT

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,

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.

Re: Output to multiple lines

Tom — Thu, 04 Apr 2013 23:14:04 GMT

Does not look at all like an application that needs or wants macro language.

Can you explain what you actually want to do?

Re: Output to multiple lines

rkong13 — Thu, 04 Apr 2013 23:27:45 GMT

Suppose you want to do the sql:

Proc sql;

create table TEST_TABLE as

select x,

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?

Re: Output to multiple lines

Tom — Fri, 05 Apr 2013 00:01:15 GMT

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;

Re: Output to multiple lines

rkong13 — Fri, 05 Apr 2013 00:49:49 GMT

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.

Re: Output to multiple lines

Patrick — Fri, 05 Apr 2013 04:14:50 GMT

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;

Re: Output to multiple lines

AjayaMuchalame — Fri, 05 Apr 2013 06:43:30 GMT

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;

Re: Output to multiple lines

rkong13 — Fri, 05 Apr 2013 18:06:44 GMT

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.

Re: Output to multiple lines

Tom — Fri, 05 Apr 2013 18:32:27 GMT

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.