DATA Step, Macro, Functions and more

How to import the dbf file having numeric file name like 102.dbf,103.dbf,etcs at the same time and merge these all.?

Reply
Occasional Contributor
Posts: 13

How to import the dbf file having numeric file name like 102.dbf,103.dbf,etcs at the same time and merge these all.?


Hi all,

How to import the dbf file having numeric file name like 102.dbf,103.dbf,etcs at the same time and merge these all.?

Regards,

RJ

Super User
Posts: 9,671

Re: How to import the dbf file having numeric file name like 102.dbf,103.dbf,etcs at the same time and merge these all.?

Did you check proc import + dbms=dbf ?

Occasional Contributor
Posts: 13

Re: How to import the dbf file having numeric file name like 102.dbf,103.dbf,etcs at the same time and merge these all.?

Hi I have used this code:

%Macor Combinefiles;

%do num = 102 %to 109;

filename avi  "C:\Users\&num..dbf";

proc dbf db4=avi out=plots_#

run;

%end;

data allplots;

set

%do num = 102 %to 109;

"C:\Users\&num..dbf";

num = 308 %to 311;

plot_&num

%end;

run;

%mend
%Combinefiles

Super User
Posts: 10,483

Re: How to import the dbf file having numeric file name like 102.dbf,103.dbf,etcs at the same time and merge these all.?

I would be very tempted to try

filename avi  "C:\Users\*.dbf";

if the files you want are the only DBF files in the folder.

Also when  using this type of construct

set

%do num = 102 %to 109;

"C:\Users\&num..dbf";

%do num = 308 %to 311; /* assuming the %do got misplaced in pasting*/

plot_&num

%end;

1) Set is not going to accept DBF files. If you want to reference the sets

then make the 102 to 109 loop similar to the 308 to 311

2) you need to close each do loop

3) You want an ; AFTER the last %end to close the SET statememt

set

%do num = 102 %to 109;

plot_&num

%end;

%do num = 308 %to 311;

plot_&num

%end;

; /* this ; closes the Set*/

Occasional Contributor
Posts: 13

Re: How to import the dbf file having numeric file name like 102.dbf,103.dbf,etcs at the same time and merge these all.?

Hi I have used this code for upload the all dbf file as well as I wish them to merge in one data set in SAS but I am getting error as:

there are 8 files 102, 103, 104...109 which have file name as number and they are having same format so how I can make data set in sas and merge them in sas itself?

Super User
Posts: 17,784

Re: How to import the dbf file having numeric file name like 102.dbf,103.dbf,etcs at the same time and merge these all.?

I'm assuming you mean you've imported all the dbf files?

SAS dataset names cannot start with numbers. I'm also assuming you mean append instead of merge.

So either the names are _102, _103 or perhaps '102'n or '103'n that you can use in your set statement.

Otherwise you'll need to clarify your question.

Occasional Contributor
Posts: 13

Re: How to import the dbf file having numeric file name like 102.dbf,103.dbf,etcs at the same time and merge these all.?

My file name are numeric like 102, 103....109..I wish to import them in sas and after importing I wish to append these all database.

Occasional Contributor
Posts: 13

Re: How to import the dbf file having numeric file name like 102.dbf,103.dbf,etcs at the same time and merge these all.?

My file name are numeric like 102, 103....109..I wish to import them in sas and after importing I wish to append these all database.

Super User
Posts: 17,784

Re: How to import the dbf file having numeric file name like 102.dbf,103.dbf,etcs at the same time and merge these all.?

proc import - RTM.

Occasional Contributor
Posts: 13

Re: How to import the dbf file having numeric file name like 102.dbf,103.dbf,etcs at the same time and merge these all.?

hi Reeza,

I have used proc import but the import shows that the file name should not be numeric.

1.Shall we use some command where we can import the entire folder and merged it or

2.Shall we change the name of file where ever the location it is and then import it by sas code?

Super User
Posts: 17,784

Re: How to import the dbf file having numeric file name like 102.dbf,103.dbf,etcs at the same time and merge these all.?

The SAS data set cannot start with a number, the file can start with a number.

Add a letter in front of the number:

proc import data=D101 ... Rest of code.

If your having issues, please include your code and log.

Occasional Contributor
Posts: 13

Re: How to import the dbf file having numeric file name like 102.dbf,103.dbf,etcs at the same time and merge these all.?

Hi Reeza,

I have used below mention code where am trying to creat sas database for the file having numeric value at below mention folder an dappend them.Please guide me.:

  %LET Path1 = C:\Users\MehtRa06\Desktop\My SAS Training\UU\Input_Files\FCTBRD Files; *Path
of File;

  %LET Start=308;

  %Let End=311;                   /*Put Start and End File name*/

Optionsmprint mlogic;

  %MACRO
IMPORT_DBF (X);

  PROC IMPORT OUT=WORK.ABC&X

  DATAFILE= "&Path1.\&X..csv"

DBMS=DBFREPLACE;

  GETNAMES=YES;

  DATAROW=2;

  GUESSINGROWS=150000;

  RUN;

  %MEND;

  %macro
Import_Fctbrd;

  %Do
i=&start
%to &End;

  %IMPORT_DBF(&i);

  %end;

  %mend;

  %Import_Fctbrd;

Super User
Super User
Posts: 6,499

Re: How to import the dbf file having numeric file name like 102.dbf,103.dbf,etcs at the same time and merge these all.?

Looks like you have gotten code to run the PROC IMPORT for each file.

To create a combined file there are a couple of choices.

1) You could add a PROC APPEND step into your looping macro.  So that after each IMPORT you append the new dataset to the end of the aggregate dataset.

%macro Import_Fctbrd(start,end);

%do i=&start %to &End;

  PROC IMPORT OUT=ABC&I

    DATAFILE= "&i..dbf"

    DBMS=DBF REPLACE;

  RUN;

  proc append base=combined data=ABC.&I force ;

  run;

%end;

%mend Import_Fctbrd;

%Import_Fctbrd(Start=308,End=311);

2) Since your datasets are using a common prefix you could use dataset list in the SET statement of a new data step to combine them.

data combined;

  set abc: ;

run;

Occasional Contributor
Posts: 13

Re: How to import the dbf file having numeric file name like 102.dbf,103.dbf,etcs at the same time and merge these all.?

Hi Tom,

Actually my file type is dbf so can you please guide for that?

Super User
Super User
Posts: 6,499

Re: How to import the dbf file having numeric file name like 102.dbf,103.dbf,etcs at the same time and merge these all.?

PROC IMPORT can read DBF files using the syntax I posted.  At least on a PC.

Ask a Question
Discussion stats
  • 21 replies
  • 572 views
  • 2 likes
  • 7 in conversation