Output to multiple lines

[rkong13]

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.

[Tom]

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

Can you explain what you actually want to do?

[rkong13]

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?

[Tom · Accepted solution]

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;

[rkong13]

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.

[Patrick]

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;

[rkong13]

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.   

[Tom]

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.

[AjayaMuchalame]

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;