Date:         Mon, 22 Dec 2008 16:53:55 -0600
Reply-To:     Mary <mlhoward@avalon.net>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mary <mlhoward@AVALON.NET>
Subject:      Re: Splitting a large dataset
Comments: To: jesper@HVIDKILDEHUSENE.DK
Content-Type: text/plain; charset="iso-8859-1"

If the whole purpose is for distribution to Excel, then you could do that on the output process
to Excel, and don't really need to split the data in SAS at all, as you don't really need it split
in SAS, just need to write out different files.   Here is a macro to do the splitting on 
the output; I've posted this before to write to different sheets in the same workbook
but here I'm moving the define of the file just before the report:

%macro do_calls_report;
ods listing close;

proc sql;
create table bylist as
select distinct isv_study_nbr, substr(isv_study_nbr,5,2) as year
from isv_injection_study_visits;
quit;
run;

proc sort data=bylist;
by year isv_study_nbr;
run;

data bylist;
set bylist;
obsnum + 1;
run;

proc sql noprint;
select count(*) into :model_count
from bylist;
quit;
%put &model_count;

%Do I = 1 %To &model_count;
proc sql noprint;
select isv_study_nbr into :sheetname
from bylist
where obsnum =&i;
quit;

data results;
set isv_injection_study_visits;
if isv_study_nbr= "&sheetname";
run;
ods tagsets.excelxp
file='C:\Work_Activities\injections_study_patients\results_&sheetname.xml'
style=analysis
options(absolute_column_width='10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10'
sheet_label=' ');
ods tagsets.excelxp options(sheet_name="&sheetname");

proc report data=results nowindows
style(report)=[rules=all cellspacing=0 bordercolor=lightgreen]
style(header)=[background=lightskyblue foreground=black]
style(column)=[background=white foreground=black vjust=top];
column
isv_study_nbr
isv_visit_location
isv_visitdate
isv_age
isv_fa;
define isv_study_nbr/display 'Study Nbr';
define isv_visit_location/display 'Visit Location';
define isv_visitdate/display 'Date of Visit';
define isv_age/display 'Age';
define isv_fa/display 'FA';

run;
ods tagsets.excelxp close;

%End ;

ods listing;
%mend;

%do_calls_report;


Also, another approach to do this would be to use VIEWS in SQL Server or in Oracle, if your
users have access to that, then each of the users has a view of the data, or it is also possible
to create views in SAS itself.  I did this when I 
used to work in Ortho and would then just write out all the views as Excel spreadsheets to their
designated locations; this way you can deal with the data as one big thing and write out the 
view to the user.   This is especially helpful if the data overlaps in areas- the diabetic area
might also get those cases with diabetic foot or frozen shoulder that are diabetes related, but the
shoulder people would get all the shoulder cases as well, so I had a master list of the patients
involved in each study and then could setup views based on those lists.  The above solution
is assuming that groups are mutually exclusive, whereas a view approach would not need that
distinction.

-Mary

  ----- Original Message ----- 
  From: jesper@HVIDKILDEHUSENE.DK 
  To: SAS-L@LISTSERV.UGA.EDU 
  Sent: Monday, December 22, 2008 3:23 PM
  Subject: Re: Splitting a large dataset


  On 22 Dec., 20:59, sash...@BELLSOUTH.NET (Paul Dorfman) wrote:
  > Jesper,
  >
  > The first advice one usually gets after asking this question is "think
  > really hard if you want to do it". This is because your data set is already
  > grouped by the key in question, and the SAS software is extremely well
  > geared for by-processing, to wit, has a rich set of tools to process each
  > group separately as if it were a separate file - thereby making it
  > unnecessary to split the file in key-named pieces in the first place.
  >
  > However, this advice might prove futile if the desired splitting is needed
  > for a reason different from processing of each subfile in the same manner,
  > the reason most often stemming from a "just do so" boss.

  Hi Paul,

  Thank you very much for your answer.

  As I said in my other answer, I've got it working, so I just want to
  tell you that there actually is a reason why I want to split the
  dataset :-)

  I have to send each person in the dataset a file containing there own
  data each month.
  The whole idea was to split the dataset and then convert it to excel-
  files, so that each person - ex James - gets an excel-file containing
  only his own data. They do not know what sas is, so I have to send
  them excel-files :-)

  Now I use the macro above to split the datasat and then use another
  macro to convert each subset into an excel-file.