topic Re: How to split and export a dataset into multiple files in SAS Procedures

How to split and export a dataset into multiple files

WesBarris — Thu, 26 Jan 2012 16:05:44 GMT

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;

Re: How to split and export a dataset into multiple files

Haikuo — Thu, 26 Jan 2012 18:27:39 GMT

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

Re: How to split and export a dataset into multiple files

Linlin — Thu, 26 Jan 2012 19:22:04 GMT

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

How to split and export a dataset into multiple files

WesBarris — Thu, 26 Jan 2012 20:02:21 GMT

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;

%mend;

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

How to split and export a dataset into multiple files

Haikuo — Thu, 26 Jan 2012 20:36:40 GMT

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;

%mend;

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

Regards,

Haikuo

How to split and export a dataset into multiple files

Linlin — Thu, 26 Jan 2012 21:01:45 GMT

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 */