Merge multiple .sas7bdat into one dataset

[Mxmaverick]

I use the code above to create around 75 datasets from xlsx files. Now i want to merge those datasets into one, is there a way around? thanks

%let dir= C:\Users\....;
%let dor= C:\Users\......;
libname orion "&dir";
libname dodo "&dor";
options compress=yes;
options notes;

%macro read_files (katalog);
%let rc=%sysfunc(filename(rawdata,&katalog));
%let did=%sysfunc(dopen(&rawdata));

%let dnum=%sysfunc(dnum(&did));

%do i=1 %to &dnum;
%let name_file=%sysfunc(dread(&did,&i));

%let rozszerz=%sysfunc(scan(&name_file,2,.));

%if &rozszerz=xlsx %then %do;
%let name_file_short=%sysfunc(scan(&name_file,1,.));

PROC IMPORT OUT=dodo.&name_file_short
DATAFILE="&katalog\&name_file" DBMS=xlsx REPLACE; sheet= Solicitudes; run;

%end;

%end;
%mend;
%read_files(&dir);

[Reeza]

Merge as in side by side, or append as in stack on top of each other? Have you verified that the variable types are aligned? Ie if you have ID as character and numeric in different data sets you'll have issues combining the data sets.

[ballardw]

---

@Reeza wrote:
Merge as in side by side, or append as in stack on top of each other? Have you verified that the variable types are aligned? Ie if you have ID as character and numeric in different data sets you'll have issues combining the data sets.

---

Not to mention character variables of differing lengths that have a strong likelihood of truncating data when combining things.

[Mxmaverick]

I just want to append, the variables are correct, no problem with that.

[Patrick]

---

@Mxmaverick wrote:

I just want to append, the variables are correct, no problem with that.

---

@Mxmaverick 

Excel doesn't have a concept of data types and data attributes like length etc. 

Proc Import will analyse your Excel source and then create appropriate SAS variables based on the values in your cells. If a single value in a cell can't get converted into a numeric variable then SAS will create a character variable - and it will use the longest string from the cells the a column to define the length of this character variable.

Soo... The same named variables in different output SAS tables will most likely have different lengths - and if you've got the slightest data quality issue in your data then even the types (character/numeric) can differ between the output tables created by the Proc Imports.

IF you know what type and lengths the variables should be then you can create a mapping table. Make sure that you define the lengths as the maximum of what you can expect. Should there ever be a source string longer than what you define then you'll end up with string truncation when combining the data.

data want;
  attrib 
    var1 length=$20
    var2 length=8 format=best32.
    ....
    ;
  stop;
run;

You then could amend your current code as follows:

%macro read_files (katalog, target=);

  %let rc=%sysfunc(filename(rawdata,&katalog));
  %let did=%sysfunc(dopen(&rawdata));
  %let dnum=%sysfunc(dnum(&did));

  %do i=1 %to &dnum;
    %let name_file=%sysfunc(dread(&did,&i));
    %let rozszerz=%sysfunc(scan(&name_file,2,.));

    %if &rozszerz=xlsx %then
      %do;
        %let name_file_short=%sysfunc(scan(&name_file,1,.));

        PROC IMPORT OUT=dodo.&name_file_short
          DATAFILE="&katalog\&name_file" DBMS=xlsx REPLACE;
          sheet= Solicitudes;
        run;
        
        proc append base=&target data=dodo.&name_file_short force nowarn;
        run; 

      %end;
  %end;
%mend;

data want;
  attrib 
    var1 length=$20
    var2 length=8 format=best32.
    ....
    ;
  stop;
run;
%read_files(&dir,target=want);

Above code is not perfect and doesn't address all possible issues - but it's may be good enough for your actual Excel sources.

IF this is not one off code but something you need to run on a regular bases for changing data sources then additional checks would be required.

[Kurt_Bremser]

---

@Mxmaverick wrote:

I just want to append, the variables are correct, no problem with that.

---

You use proc import, which means this is impossible in the long run. Sooner or later you'll have longer content in a spreadsheet that is truncated because of shorter content in an earlier file.

You can guard against that by setting up a dummy dataset that defines the structure and is used as the first one when concatenating. But that only works until a column changes its type.

[Reeza · Accepted solution]

---

@Mxmaverick wrote:

I just want to append, the variables are correct, no problem with that.

---

If that is true, then just list them out.

data want;
set have1 have2 have3 have4 have5 ...;

run;

If you have systematic names you can avoid listing all using:

1. Same prefix - this includes all data sets that start with the prefix HAVE

set have: ;

2. Sequential data - must be ordered

set have1-have99;

If the names vary but all are in the same library:

proc sql noprint;
select catx('.', 'mylib', name) into :name_list separated by " "
from sashelp.vtable 
where libname='MYLIB';
quit;

data want;
set &name_list;
run;

[Mxmaverick]

I finished by using this one:

data tot_comb;
set orion.total_:;
run;

Many thanks!!!!!!