How to split and export a dataset into multiple files

[WesBarris]

I have a dataset that includes one column whose value are integers [1-10].  I would like to

save this dataset into 10 dbf files where each dbf file contains all data corresponding to

one of those 10 values.  For example, suppose I had this data:

Key Value

1     this

2     that

1     here

2     there

Is there a way to write this data into two dbf files where one contains keys of "1" and the

other containd keys of "2"?  I know that I could manually extract all data from a large

dataset once for each possible value of "key" and then save that temporary dataset.  I

was just wondering if there was a more automated way of doing this.  I know the following

code does not work but I was thinking something like this:

data largedata;

proc export outfile="&basename.key.dbf" dbms=dbf; by key;

run;

[Haikuo]

Here is a smaller Macro hopefully to tackle your specific problem.

data have;

infile cards;

input Key Value $;

cards;

1     this

2     that

1     here

2     there

;

proc sql;

select distinct key into:key separated by ' '

from have;

quit;

%macro dist;

%doi=1 %to %sysfunc(countw(&key));

data want&i;

set have;

where key=%scan(&key,&i);

run;

%end;

%mend;

%dist

Kindly Regards,

Haikuo

[Linlin]

Hi,

I know nothing about dbf files. the code below was able to create separated excel files by variable "key".

data have;

input Key :Value $;

cards;

1     this

2     that

1     here

2     there

3     this

4     that

5     here

5     there

;

proc sql noprint;

  select distinct key into :keys separated by ' '

    from have

        order by key;

quit;

%macro test;

%do i=1 %to %sysfunc(countw(&keys));

%let k=%sysfunc(scan(&&keys,&i));

   data want;

     set have;

       if key="&k";

PROC EXPORT DATA= WORK.want

            OUTFILE= "C:\TEMP\t_&k..xls"

            DBMS=EXCEL REPLACE;

     SHEET="list";

RUN;

%end;

%mend;

%test

/*  to create dbf files */

proc sql noprint;

  select distinct key into :keys separated by ' '

    from have

        order by key;

quit;

%macro test;

%do i=1 %to %sysfunc(countw(&keys));

%let k=%sysfunc(scan(&&keys,&i));

   data want;

     set have;

       if key="&k";

PROC EXPORT DATA= WORK.want

            OUTFILE= "C:\TEMP\t_&k..dbf"

            DBMS=dbf REPLACE;

RUN;

%end;

%mend;

%test

Message was edited by: Yulin Liu

[WesBarris]

Thank you.  Your example has helped me get most of the way there.  I have complicated

it slightly by keying off of two variables instead of one.  With two variables there is the

possibility of an empty dataset.  Is there a way to only do a proc export when the number

of observations is greater than zero?

data have;

infile cards;

input hatch source $ Value $;

cards;

1   A   this

2   A   that

1   B   here

2   C   there

;

proc sql noprint;

select distinct hatch into:hatches separated by ' ' from have;

select distinct source into:sources separated by ' ' from have;

quit;

%macro splitData(hatches=, sources=, basename=);

%do i=1 %to %sysfunc(countw(&hatches));

   %do j=1 %to %sysfunc(countw(&sources));

      %let hat=%scan(&hatches,&i);

      %let src=%scan(&sources,&j);

      data &basename&hat&src;

      set have;

      where hatch=&hat and source="&src";

      proc export outfile="&basename&hat&src..dbf" dbms=dbf replace;

      run;

   %end;

%end;

%mend;

%splitData(hatches=&hatches, sources=&sources, basename=test)

[Haikuo]

I have not tested it. But you may get the idea:

%macro splitData(hatches=, sources=, basename=);

%do i=1 %to %sysfunc(countw(&hatches));

   %do j=1 %to %sysfunc(countw(&sources));

      %let hat=%scan(&hatches,&i);

      %let src=%scan(&sources,&j);

      data &basename&hat&src;

      set have;

      where hatch=&hat and source="&src";

run;

data _null_;

if e>0 then call execute('proc export data=&basename&hat&src outfile="&basename&hat&src..dbf" dbms=dbf replace;run;');

     set &basename&hat&src nobs=e;

      run;

   %end;

%end;

%mend;

%splitData(hatches=&hatches, sources=&sources, basename=test)

Regards,

Haikuo

[Linlin · Accepted solution]

data have;

infile cards;

input hatch source $ Value $;

cards;

1   A   this

2   A   that

1   B   here

2   C   there

;

proc sql noprint;

select distinct hatch into:hatches separated by ' ' from have;

select distinct source into:sources separated by ' ' from have;

quit;

%macro splitData(hatches=, sources=, basename=);

%do i=1 %to %sysfunc(countw(&hatches));

   %do j=1 %to %sysfunc(countw(&sources));

      %let hat=%scan(&hatches,&i);

      %let src=%scan(&sources,&j);

      data &basename&hat&src;

      set have;

      where hatch=&hat and source="&src";

      run;

      %let obs=;

      data _null_;

        if _n_=1 then

        set  &basename&hat&src nobs=nobs;

        call symputx('obs',nobs);

      run;

      %if &obs>0 %then %do;

      proc export outfile="&basename&hat&src..dbf" dbms=dbf replace;

      run;

   %end; %end;

%end;

%mend;

%splitData(hatches=&hatches, sources=&sources, basename=test)

/* I add

%let obs=;

      data _null_;

        if _n_=1 then

        set  &basename&hat&src nobs=nobs;

        call symputx('obs',nobs);

      run;

      %if &obs>0 %then %do;

…

%end;

to your code */