topic Re: Merge data sets by using macro in SAS Programming

Merge data sets by using macro

liyongkai800 — Wed, 18 Sep 2019 18:45:33 GMT

Hi folks,

Say I have data sets: Onedata1, Onedata2, Twodata1, Twodata2, Threedata1, Threedata2, which have the same structure.

I can simply merge them like this:

Data combine;

set Onedata1 Onedata2 Twodata1 Twodata2 Threedata1 Threedata2;

run;

However, I'm wondering is there an alternative way to merge them, to make loops on one to three and 1 to 2, so that I don't need to list all of them after set statement. Thanks.

Re: Merge data sets by using macro

novinosrin — Wed, 18 Sep 2019 18:49:50 GMT

I find loops creating texts boring

why not?

 

Data combine;

set Onedata:  Twodata:Threedata:;

run;

If you have just sets of 2

Re: Merge data sets by using macro

liyongkai800 — Wed, 18 Sep 2019 18:59:35 GMT

Thanks, that does save 1 to 3. However, the texts actually mean something in my original question, and it has more than three groups. Then I still need something like onedata:...... twentydata:;

Re: Merge data sets by using macro

novinosrin — Wed, 18 Sep 2019 19:04:00 GMT

Okay @liyongkai800 you do makes sense. If the texts are in a library, you could query dictionary tables and load the values in a macro var and call in the set statement. If there is a pattern in the names. the filtering can be easy. But either way, it's gonna be a tedious and not so cool.

Please do one thing, post a more "representative" sample of your names and somebody might have a better idea. Chill

Re: Merge data sets by using macro

ballardw — Wed, 18 Sep 2019 22:36:29 GMT

@liyongkai800 wrote:
Thanks, that does save 1 to 3. However, the texts actually mean something in my original question, and it has more than three groups. Then I still need something like onedata:...... twentydata:;

What texts? Your original question doesn't reference any "texts".

If you mean something related to the name of the contributing data set you can add that with the Indsname option:

Data combine;
   set Onedata:  Twodata:Threedata: indsname=ds;
   source = ds;
run;

DS would be a temporary variable that exists only for the duration of the data step. It holds the name of the data set the current record is from.

I assigned the value to another variable so you could see it after the step ends. You could parse your set name for any processing you might need that is different based on the name.

Or perhaps assign a custom format to the text to display considerably longer text then the variable would have.

Re: Merge data sets by using macro

PaigeMiller — Wed, 18 Sep 2019 22:46:18 GMT

Yes, you can do this with a macro or CALL EXECUTE.

But you need to be more specific about the problem. Are you talking about ALL of the data sets in a library, or some? If some, how would you identify the ones that are to be merged? Is all of this merging done one-to-one, or BY some variable?

Re: Merge data sets by using macro

liyongkai800 — Mon, 23 Sep 2019 12:58:57 GMT

Yes, dictionary.tables can be used to create a list to store all data sets. I missed some features in my question.