Output by a specific variable

[deleted_user]

I have a need to split my output into different tables (or exported Excel files) depending on a specific variable.

say my data looks like this:

Sales | Name | email
5000 | Bob | bob@email.com
6000 | Jane| Jane@email.com
2000 | Joe | Joe@email.com

how can I split my output based on the name, so that I would get 3 different tables (preferably Excel)?

I'm not too savvy with the code writing piece, so any handholding would be greatly appreciated.

[DerekAdams]

Hi Magnus.

Bit rough and ready..

%macro xls(criteria);
options orientation=portrait missing=" ";
ods noresults;
ods html
style=minimal
file="\\yourpath\&criteria..xls";

PROC print DATA=WORK.test (where=(upcase(name)= "&criteria"));
RUN;

TITLE; FOOTNOTE;

ods html close;
%mend;

%xls(BOB);
%xls(JANE);
%xls(JOE);

holliesmate
NZ

[Cynthia_sas]

I was going to suggest something a bit simpler without any macro coding:
[pre]

ods csv path='c:\temp'
file='peeps1.csv' newfile=bygroup;

proc print data=salesinfo;
by name;
run;

ods csv close;
[/pre]

Then, if every person was one observation then every name would get a new file, starting with peeps1.csv, peeps2.csv, peeps3.csv, etc. The only downside is that if the file has 2 Joes or 2 Johns, the obs with the same name would all go into 1 file. So I was counting on the data actually having unique names -- such as as a "full name" field.

The macro solution is nice because it allows you to use &CRITERIA as the name of the output file. This solution still has the issue with 2 Joes, but otherwise, gives more control over the name.

cynthia

[deleted_user]

someone on SAS-L asked how to macro-ise running proc means for each value of a "name". I offered an ods option through by-group processing very similar to Cynthia's (but not as neat). What my method added was the renaming of files from the default name1, name2..... nameNNN with the name in the by-value....
The whole posting can be seen at http://listserv.uga.edu/cgi-bin/wa?A2=ind0901d&L=sas-l&P=27266 . The final part replacing default names with class value/by-group value, is adapted here for Cynthia's example (except I would name her first file peeps1000.csv:[pre]data _null_ ;
length dir1 $8 odsnm $100 ;
* prepare a command file for rename commands;
file 'c:\temp\rens.bat' ;
* the first command prepares "current folder" ;
put "cd /d ""c:\temp"" ";
rc = filename( dir1, 'c:\temp' );
di = dopen( dir1 );
memcount= dnum( di ) ;
do memn= 1 to memcount ;
odsnm = dread( di, memn );
if scan( odsnm, -1, '.' ) ne 'csv' then continue ;
if odsnm ne: 'peeps' then continue ;
do until( last.name ) ;
set salesinfo ;
by name ;
end;
nmq = quote( trim( odsnm));
newq= quote( trim( name )!!'.csv' ) ;
put 'ren ' nmq +1 newq ;
end;
rc = dclose( di );
rc = filename( dir1, ' ' );
stop;
run ;[/pre]Then to execute that command file rens.bat, I chose to demonstrate call system, with[pre]option noxwait xsync ;
data _null_ ;
call system( 'c:\temp\rens.bat' ) ;
stop;
run ;[/pre]
Beware that this version adapted for CSV files, I isn't tested (yet).

Hope you'll find it interesting

PeterC