topic Re: Can SAS do this when merging datasets? in SAS Programming

Can SAS do this when merging datasets?

Neal0801 — Mon, 24 Oct 2016 18:47:23 GMT

have around 100 datasets that contains 30 stocks.Now, I have to open each dataset manually and merge them if they contain same stock. This is really time-consuming. So I want to ask whether I could use some codes to achieve this (merge datasets based on certain requirements).

p.s. each dataset has a variable called RIC that could identify what the stock (e.g AXP and BP) is involved in this dataset.

Re: Can SAS do this when merging datasets?

Reeza — Mon, 24 Oct 2016 19:31:55 GMT

You can filter in a data step merge.

Are you merging datasets side by side, or appending (stacking datasets on top of each other)?

Re: Can SAS do this when merging datasets?

ballardw — Mon, 24 Oct 2016 19:55:18 GMT

If you are stacking, adding data set one after the other, and the data set names are somewhat common, such as all start with the same 5 characters you could do something like:

data want;

set mylib.ABCDE: ;

where RIC in ('AXB', 'BP');

run;

The : at the end says to read all the sets that start with ABCDE in the name that are in library MYLIB.

If you have a few different stems then have all of them on the set statement:

set mylib.ABCDE: mylib.PDQ: ;

The sets could even be in multiple libraries.

Re: Can SAS do this when merging datasets?

Shmuel — Mon, 24 Oct 2016 19:59:15 GMT

There are some points to clarify:

1) is there a stock_id in each dataset ?

2) assuming there is a stock_id, is there only one row per stock_id or may be more ?

3) are other variables of same name in all datasets ? if positive -

which value sholud be in the output in case of same stock_id in more than one dataset ?

In case of one row per stock_id in a dataset of each datasets and

variable names are different in datasets or you don't mind which dataset contribute to output

then you can merge all of them in one data step:

data all_stocks;

merge data1

data2

....

; by stock_id;

... any other code including filtering ...

run;