BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
WesBarris
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

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

View solution in original post

5 REPLIES 5
Haikuo
Onyx | Level 15

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
Lapis Lazuli | Level 10

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
Obsidian | Level 7

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
Onyx | Level 15

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
Lapis Lazuli | Level 10

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 7535 views
  • 6 likes
  • 3 in conversation