DATA Step, Macro, Functions and more

Set All Datasets in a library in a datastep

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

Set All Datasets in a library in a datastep

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


Accepted Solutions
Solution
‎01-08-2012 10:20 AM
PROC Star
Posts: 7,468

Re: Set All Datasets in a library in a datastep

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


All Replies
Super Contributor
Posts: 1,636

Re: Set All Datasets in a library in a datastep

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

SAS Employee
Posts: 104

Set All Datasets in a library in a datastep

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;
Solution
‎01-08-2012 10:20 AM
PROC Star
Posts: 7,468

Re: Set All Datasets in a library in a datastep

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.

Super User
Posts: 5,424

Set All Datasets in a library in a datastep

Works in 9.2 as well!Smiley Happy

Data never sleeps
Occasional Contributor
Posts: 17

Set All Datasets in a library in a datastep

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

Learner
Posts: 1

Re: Set All Datasets in a library in a datastep

Exactly what I needed!  Thanks!

New Contributor
Posts: 3

Re: Set All Datasets in a library in a datastep

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.

Super User
Posts: 11,343

Re: Set All Datasets in a library in a datastep

Posted in reply to SubhenduKumar

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.

Respected Advisor
Posts: 3,799

Re: Set All Datasets in a library in a datastep

Posted in reply to SubhenduKumar

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.

New Contributor
Posts: 3

Re: Set All Datasets in a library in a datastep

Posted in reply to data_null__

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

Respected Advisor
Posts: 3,799

Re: Set All Datasets in a library in a datastep

Posted in reply to SubhenduKumar

Then why do the variables have different attributes?

SubhenduKumar wrote:

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

New Contributor
Posts: 3

Re: Set All Datasets in a library in a datastep

Posted in reply to data_null__

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.

Respected Advisor
Posts: 3,799

Re: Set All Datasets in a library in a datastep

Posted in reply to SubhenduKumar

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 8696 views
  • 4 likes
  • 9 in conversation