topic Re: How to merge dynamic datasets? in SAS Programming

How to merge dynamic datasets?

Sica — Wed, 25 Nov 2020 17:43:37 GMT

Hello!!

I am quite new in SAS programming, so I have some doubts how to merge dynamic datasets.

I have a table containing a variable with the names of multiple datasets I would like to merge into one single dataset.

I would like to know how I can merge all these datasets. All of them are placed in the same library.

Thank you!!

Re: How to merge dynamic datasets?

Reeza — Wed, 25 Nov 2020 17:51:38 GMT

Is the merge complicated or just a simple BY statement?

proc sql noprint;
select name into :merge_list separated by " " from listDatasets;
quit;

data want;
merge &merge_list;
by commonVariable;
run;

Re: How to merge dynamic datasets?

ballardw — Wed, 25 Nov 2020 17:52:47 GMT

First question: Merge or Append?

Merge in SAS usually refers to "horizontal" combining such as matching on a value.

Append would mean "add to the end of" an existing data set.

Second: Do variables of the same names have the same properties? If variable X is not numeric or character (either one) in all of the sets you will have a failure. If common variables are character you can get truncated data.

Best is provide some examples of the data and what the result should be.

Re: How to merge dynamic datasets?

Sica — Wed, 25 Nov 2020 18:10:28 GMT

Sorry, I would like to append.

All the tables have the same columns. The library has more than 1000 datasets, and I would like to append around 80 of them. So I created a variable containing all the datasets I would like to append, but I do know how to it dynamically, only by typing dataset by dataset =/
I would like to do it in a smarter way.

Thank you.

Re: How to merge dynamic datasets?

ballardw — Wed, 25 Nov 2020 18:21:05 GMT

@Sica wrote:
Sorry, I would like to append.

All the tables have the same columns. The library has more than 1000 datasets, and I would like to append around 80 of them. So I created a variable containing all the datasets I would like to append, but I do know how to it dynamically, only by typing dataset by dataset =/
I would like to do it in a smarter way.

Thank you.

It isn't clear what you mean by a "variable containing all the datasets".

If you mean that you have a dataset containing the names you want as one observation per name then something like this will work if there are not problems with variable properties the same. Note, not efficient but simple code:

proc sql;
   select datasetnames into : namelist separated by ' '
   from datasetwithnames
   ;
run;

data want;
   set &namelist.;
run;

Or if the names are "nice" such as common base name ending with sequence number you could use a list like

data want;
   set dsname1 - dsname10;
run;

There are other ways to build lists depending on the values. It might help to show the names you are concerned with so we don't have to make a lot of guesses.

Re: How to merge dynamic datasets?

Reeza — Wed, 25 Nov 2020 18:23:50 GMT

Naming conventions would make this trivial so I'd highly recommend you pick a good naming convention.

Here is a reference that illustrates how to refer to variables and datasets in a short cut list:
https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

And a slight modification to @ballardw code in case you don't have the library included in the list of data set names.

%let myLibname = sashelp;
proc sql;
   select catx(".", "&myLibname.", datasetnames) into : namelist separated by ' '
   from datasetwithnames
   ;
run;

data want;
   set &namelist.;
run;

Re: How to merge dynamic datasets?

Kurt_Bremser — Wed, 25 Nov 2020 18:25:45 GMT

If you can state a rule for selecting the datasets, then that rule can be made into code.

Re: How to merge dynamic datasets?

Sica — Wed, 25 Nov 2020 19:34:41 GMT

Thank you a lot!!

Is there an easy way to create a column and add the original file name from all values?

Re: How to merge dynamic datasets?

Reeza — Wed, 25 Nov 2020 19:43:40 GMT

INDSNAME option on the SET statement + save it.

data want;
set ..... INDSNAME = source;
dataSource = source;
run;

Re: How to merge dynamic datasets?

Sica — Wed, 25 Nov 2020 19:50:48 GMT

Reeza, thanks a lot! It woks!! =o)

Re: How to merge dynamic datasets?

ballardw — Wed, 25 Nov 2020 19:53:10 GMT

@Sica wrote:
Thank you a lot!!

Is there an easy way to create a column and add the original file name from all values?

The Set statement option INDSNAME name will do that. The default behavior is to create a temporary variable, so you need to assign that value to a desired variable.

Example:

data junk;
   set sashelp.class indsname=ds;
   source=ds;
run;

DS is a temporary variable that holds the library and dataset name and will not be written to the output data set (that's what temporary means). Source is assigned the value of the variable DS and will be kept in the data. You only use the option one time and it really doesn't matter where it goes on the set statement.