Help using Base SAS procedures

Output to multiple lines

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

Output to multiple lines

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.


Accepted Solutions
Solution
‎04-04-2013 08:01 PM
Super User
Super User
Posts: 6,499

Re: Output to multiple lines

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;


 

View solution in original post


All Replies
Super User
Super User
Posts: 6,499

Re: Output to multiple lines

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

Can you explain what you actually want to do?

Occasional Contributor
Posts: 19

Re: Output to multiple lines

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?

Solution
‎04-04-2013 08:01 PM
Super User
Super User
Posts: 6,499

Re: Output to multiple lines

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;


 

Occasional Contributor
Posts: 19

Re: Output to multiple lines

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.

Respected Advisor
Posts: 3,887

Re: Output to multiple lines

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;

Occasional Contributor
Posts: 19

Re: Output to multiple lines

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.   

Super User
Super User
Posts: 6,499

Re: Output to multiple lines

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.

New Contributor
Posts: 2

Re: Output to multiple lines

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;

☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 353 views
  • 3 likes
  • 4 in conversation