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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.