BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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.
3 REPLIES 3
DerekAdams
Calcite | Level 5
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
Diamond | Level 26
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
Not applicable
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

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1170 views
  • 0 likes
  • 3 in conversation