Can SAS do this when merging datasets?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Can SAS do this when merging datasets?

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.


Accepted Solutions
Solution
‎10-30-2016 09:41 AM
Grand Advisor
Posts: 10,210

Re: Can SAS do this when merging datasets?

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.

View solution in original post


All Replies
Grand Advisor
Posts: 17,338

Re: Can SAS do this when merging datasets?

You can filter in a data step merge.

 

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

Super User
Posts: 1,231

Re: Can SAS do this when merging datasets?

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;

 

Solution
‎10-30-2016 09:41 AM
Grand Advisor
Posts: 10,210

Re: Can SAS do this when merging datasets?

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.

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 244 views
  • 0 likes
  • 4 in conversation