Desktop productivity for business analysts and programmers

Output by a specific variable

Not applicable
Posts: 0

Output by a specific variable

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 |
6000 | Jane|
2000 | Joe |

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.
Posts: 30

Re: Output by a specific variable

Posted in reply to deleted_user
Hi Magnus.

Bit rough and ready..

%macro xls(criteria);
options orientation=portrait missing=" ";
ods noresults;
ods html

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


ods html close;


Posts: 9,431

Re: Output by a specific variable

Posted in reply to DerekAdams
I was going to suggest something a bit simpler without any macro coding:

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

proc print data=salesinfo;
by name;

ods csv close;

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.

Not applicable
Posts: 0

Re: Output by a specific variable

Posted in reply to Cynthia_sas
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 . 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( ) ;
set salesinfo ;
by name ;
nmq = quote( trim( odsnm));
newq= quote( trim( name )!!'.csv' ) ;
put 'ren ' nmq +1 newq ;
rc = dclose( di );
rc = filename( dir1, ' ' );
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' ) ;
run ;[/pre]
Beware that this version adapted for CSV files, I isn't tested (yet).

Hope you'll find it interesting

Ask a Question
Discussion stats
  • 3 replies
  • 3 in conversation