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;
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 */
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
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
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)
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
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 */
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.