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.
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.
You can filter in a data step merge.
Are you merging datasets side by side, or appending (stacking datasets on top of each other)?
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;
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.