Subject: Q: Writing selections of output to separate files Summary: An answer to the splitting problem. Respondent: Ian Whitlock <whitloi1@westat.com>

K. Anthony Eder <aeder@HSD.UVIC.CA> asks

>I would like to know how (if possible) to have some ouput saved to >individual files. Specifically I have a regression being run with a BY >variable which then outputs 20 separate sets of results. I would like to >save the estimated coefficients and their standard errors (for each BY >regression) into one file or, ideally, 20 separate files. Any advice?

Asking for special things like this means doing a little extra work. At least SAS provides the tools. Take the output coefficient file and split it in a DATA step. Here is some sample code that will handle the problem in a perfectly general fashion.

Let's suppose there in one by variable, BYGRP with numeric values. Make a format to change from numbers to member names.

proc format ; value bymems 1 = 'lib.abc' 2 = 'lib.def' .... ; run ;

Now use a little SQL. (Pardon my choice, when SAS-L is generating so much anti-SQL talk these days, but really one has to choose the right tool for the task.)

proc sql noprint ; create view vw as select distinct put ( bygrp , bymems17. ) as mem from outest ; select mem into : memlist separated by ' ' from vw ; select 'when ( "' || mem || '") output ' || mem || ';' into : whenlist separated by ' ' from vw ; run ;

The SQL code creates to macro variables, MEMLIST and WHENLIST, which hold the variable part of the next splitting DATA step. (OOPS! I didn't want to add to the SQL injury with the dreaded macro, but I cannot help using the right tool for the job.)

data &memlist ; set outest ; select ( put ( bygrp, bymems17. ) ) ; &whenlist otherwise error ; end ; run ;

Is the code readable? Well that is in the eye of the beholder. Is it maintainable? Consider writing a split program without SQL and macro variables? Now run 20 regression models using different by groups splitting each model. I bet there will be fewer and simpler changes to the above code than anything that can be done without the above tools. Can it be improved? Of course the SQL code and subsequent DATA step should be housed in a macro SPLIT with parameters. Changing the point of view slightly the call might be

%split ( data = outest1 , outlib = lib , byval = put(bygrp,bymem3.) )

The second call might be

%split ( data = outest2 , outlib = work , byval = x || put ( year , monny5. ) || put ( y , 1. ) )

and so on.

Is it efficient? I just wrote the above code, but I cold have used an an in house off the shelf macro instead. You mean computer time efficiency? Well no, you can probably save up to 20 seconds by writing out the split DATA step and skipping the format, and SQL code. Now let me ask one. How much work are you willing to do to save 20 seconds? Are you willing to say "No SQL, no macro, make it simple!"?

Ian Whitlock <whitloi1@westat.com>