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

Hi,

I want to try and union all datasets in a temporary library together either via datastep, proc append or proc datasets etc.  Is there a native method or known technique to do this?  I'm pretty sure I can create something with populating an array or control table via dictionary tables or metadata but was wondering if there is something similar to the use of dos wildcards(:*) like in filename statements or a reserverd statements like _all_ etc.

Much appreciated

Cam

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

Cam,  If you are on 9.3 and you can ensure that all of the files begin with the same name prefix (e.g., temp, or _ or any distinguishing character of set of characters), then you can just use a name prefix with the : wildcard. e.g.:

data want;

  set _:;

run;

would concatenate all of the files that begin with an underscore.

View solution in original post

13 REPLIES 13
Linlin
Lapis Lazuli | Level 10

/* creating sample data */

data have1 have2 have3;

do i=1 to 5;

output;

end;

run;

/* put all the dataset names in temp library into macro variable &names*/

proc sql noprint;

  select memname into :names separated by ' '

    from dictionary.tables

            where libname='WORK';

quit;

/* create final dataset*/

data want;

  set &names;

run;

proc print;run;

                                          Obs     i

                                               1    1

                                               2    2

                                               3    3

                                               4    4

                                               5    5

                                               6    1

                                               7    2

                                               8    3

                                               9    4

                                              10    5

                                              11    1

                                              12    2

                                              13    3

                                              14    4

                                              15    5

Linlin

SASJedi
SAS Super FREQ

Here is an approach using a DATA _NULL_ step to write the data step you want, and then run it via CALL EXECUTE:

 
data _null_; 
   length code $32767;
   retain CODE ;
   /* Read the metadata view to get the data set names */
   set sashelp.vtable END=LAST;
   where libname='WORK';
   /* On the first iteration, start writing the data step code */
   if _n_=1 then do;
      CODE="DATA WANT; SET ";
   END;
   /* Add the name of each data set to the code you are building */
   CODE =CATX(' ',CODE,CATX('.',LIBNAME,MEMNAME));
   /* On last iteration, finish the DATA step code, then CALL EXECUTE to run it */
   IF LAST THEN DO;
     CODE=CATS(CODE,";RUN;");
      CALL EXECUTE(CODE);
   END;
RUN;
Check out my Jedi SAS Tricks for SAS Users
art297
Opal | Level 21

Cam,  If you are on 9.3 and you can ensure that all of the files begin with the same name prefix (e.g., temp, or _ or any distinguishing character of set of characters), then you can just use a name prefix with the : wildcard. e.g.:

data want;

  set _:;

run;

would concatenate all of the files that begin with an underscore.

LinusH
Tourmaline | Level 20

Works in 9.2 as well!Smiley Happy

Data never sleeps
CameronL
Fluorite | Level 6

Thanks Art and Linus.  I was hoping the : wildcard would work somehow.  You are correct Linus, works a charm in 9.2.  Many thanks

clstr
Calcite | Level 5

Exactly what I needed!  Thanks!

SubhenduKumar
Calcite | Level 5

Hi,

I have 1000 datasets which contains 10 columns in all datasets. But the problem is with the those columns having different datatypes for different datasets, so its throwing error while appending those. if you could help me out in this situation.

ballardw
Super User

One: you should start a new topic as your issue is different than the original post.

Two: You can't append variables of different types. You will need to either 1) change the names or 2) change the types. Since the data type of existing variables can't be changed directly

If the structure of the files datasets is supposed to be the same but data types vary I would be concerned that some of your numeric variables should have been character but due to how the data was brought into SAS may have discarded some values. This is likely to be the case if Proc Import was used with Excel source data files. I would verify that the original data is complete before even attempting to concatenate the data sets.

data_null__
Jade | Level 19

How did you create the 1000 data sets.

  • 1000 Excel
  • 1000 csv or other flat file
  • 1000 PROC IMPORTs.

If you have flat files it might be easier to write a data step program to read the data into one data set.

It might even be easier to write all the data in the 1000 data set out to 1000 files but that would be only if you don't have raw data.

Another approach would be to collect PROC CONTENTS data from all 1000 and group for data with like attributes then combine the data in groups and address the attribute differences in what might be just a few data sets.

SubhenduKumar
Calcite | Level 5

Hi,I have created those data sets by using sas macro.

data_null__
Jade | Level 19

Then why do the variables have different attributes?

SubhenduKumar wrote:

Hi,I have created those data sets by using sas macro.

SubhenduKumar
Calcite | Level 5

Because i have picked the data from different tables to create those data sets.

And i am trying now with force append.

PROC APPEND BASE=<base-SAS-data-set> DATA=<Child dataset> <FORCE>;

which is working for 2 data sets, But i want to append all data sets present in my library.

data_null__
Jade | Level 19

Seems like you should fix the issue in your macro.  Perhaps just convert all variables to character.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 13 replies
  • 20246 views
  • 6 likes
  • 9 in conversation