topic Re: Simplify data merge code in SAS Programming

Simplify data merge code

afiqcjohari — Fri, 07 Apr 2017 07:00:26 GMT

proc sort data=have1;by var1;run;
data have2 (rename =(var1er=var1));set have2; run;
proc sort data=have2;by var1;run;

data want;
merge have1(in=a) have2(in=b);
by var1;
if a;
run;

Is it possible to simplify this?

Re: Simplify data merge code

Jagadishkatam — Fri, 07 Apr 2017 06:49:46 GMT

Not sure, in the code the statement 'if a' is doing anything.

we could remove the same as there is no by statement.

Re: Simplify data merge code

afiqcjohari — Fri, 07 Apr 2017 07:00:59 GMT

Edited for the by statement.

Re: Simplify data merge code

Kurt_Bremser — Fri, 07 Apr 2017 07:19:53 GMT

No extra step for the rename is needed:

proc sort data=have1;
by var1;
run;

proc sort data=have2;
by var1er;
run;

data want;
merge
  have1 (in=a)
  have2 (
    in=b
    rename=(var1er=var1)
  )
;
by var1;
if a;
run;

Re: Simplify data merge code

afiqcjohari — Fri, 07 Apr 2017 08:10:01 GMT

Is it possible to take out the sorting part? Could it be done within the data step merge?

Re: Simplify data merge code

RW9 — Fri, 07 Apr 2017 08:42:26 GMT

Something like:

proc sql;
  create table WANT as
  select A.VAR1ER as VAR1,
           B.*
  from   HAVE1 A
  left join HAVE2 B
  on      A.VAR1=B.VAR1;
quit;

Re: Simplify data merge code

Kurt_Bremser — Fri, 07 Apr 2017 08:47:35 GMT

@afiqcjohari wrote:
Is it possible to take out the sorting part? Could it be done within the data step merge?

To merge, you always have to sort. Even if you use proc sql and you do not have to sort explicitly, the sql procedure will do it automatically, which basically uses the same resources and needs the same time. If you need the same order repeatedly, an explicit sort is the way to go.

Re: Simplify data merge code

afiqcjohari — Fri, 07 Apr 2017 08:48:26 GMT

Thank you. But one of the feature I like about SAS is the data merge step. It's actually pretty elegant minus the need to sort step. :). Actually the real code outputs to many tables which data merge is very good at. Writing in proc sql would mean 2 different proc sql for 2 different tables.

Re: Simplify data merge code

RW9 — Fri, 07 Apr 2017 08:52:52 GMT

Well, SQL was developed to handle relational databases, so combining data is the key functioanlity of it. True there are times when either can be better. As far as this question, we only see a tiny part of the problem apparently, so can only guess.

Re: Simplify data merge code

afiqcjohari — Fri, 07 Apr 2017 08:54:07 GMT

@Kurt_Bremser wrote:
@afiqcjohari wrote:
Is it possible to take out the sorting part? Could it be done within the data step merge?
To merge, you always have to sort. Even if you use proc sql and you do not have to sort explicitly, the sql procedure will do it automatically, which basically uses the same resources and needs the same time. If you need the same order repeatedly, an explicit sort is the way to go.

The initial code was in proc sql, but I prefer data step merge because it can create multiple tables. Had it been in proc sql, I need to write separate proc sql for each different table. But I really want to avoid writing the 2 proc sorts, if possible. But it seems that it's not. What I don't get is, why if data merge necessitates the 2 tables to be sorted by the common variable, shouldn't data merge internally 'know' that the 2 tables will need to be sorted? Meaning, data merge should by default sort these 2 variables implicitly.

Is there a case where data merge could be called without the tables being sorted in the first place?

Re: Simplify data merge code

afiqcjohari — Fri, 07 Apr 2017 08:57:40 GMT

I really appreciate your answer. What I don't understand is why does SAS require the programmer to explicitly write the 2 proc sorts on the 2 tables? Why can't data merge does it at one go? Because as per my understanding, data merge requires the tables to be sorted. It could have been more concise if data merge can implicity sort the tables by itself.

Re: Simplify data merge code

Kurt_Bremser — Fri, 07 Apr 2017 09:03:44 GMT

The merge statement in a data step is very simple and relies on the given order of the datasets. This is its natural behaviour. Therefore the sort is necessary if you use by with a merge.

This simplicity is one of the reasons why sort & merge often outperforms SQL (which sorts implicitly).

A proc sort is usually three lines of code. I type that without really thinking in < 10 seconds. And I'm no touch-typist. To make your work easier, you can define a keyboard macro in EG that writes the sort step for you, so you only have to fill in the dataset and the by variables.

Re: Simplify data merge code

Kurt_Bremser — Fri, 07 Apr 2017 09:06:02 GMT

@afiqcjohari wrote:

I really appreciate your answer. What I don't understand is why does SAS require the programmer to explicitly write the 2 proc sorts on the 2 tables? Why can't data merge does it at one go? Because as per my understanding, data merge requires the tables to be sorted. It could have been more concise if data merge can implicity sort the tables by itself.

Misconception. Merge on its own does NOT require the datasets to be sorted. Merge with a by does need sorting.

Re: Simplify data merge code

Astounding — Fri, 07 Apr 2017 11:27:28 GMT

Perhaps an important point .... MERGE with a BY statement requires that your data set is already in order before the DATA step begins. If it is already in order without running PROC SORT, that is sufficient. You only need to run PROC SORT when the data is not in the right order.

Re: Simplify data merge code

ballardw — Fri, 07 Apr 2017 15:37:21 GMT

Also the BY statement in a data step is used with other things than just MERGE, UPDATE or MODIFY statements in a data step.

And the BY statement supports the NOTSORTED and DESCENDING options and trying to "guess" all the time which combination a user "intended" might be very problematical.

And since a basic sort statement is 3 lines of code including the "run;" I am surprised this is much of an issue at all.

Re: Simplify data merge code

afiqcjohari — Mon, 10 Apr 2017 07:02:14 GMT

How is it possible to merge without a by statement? Otherwise how would merge know what is the variable(s) to use to merge the different tables?

Re: Simplify data merge code

Kurt_Bremser — Mon, 10 Apr 2017 07:40:35 GMT

@afiqcjohari wrote:
How is it possible to merge without a by statement? Otherwise how would merge know what is the variable(s) to use to merge the different tables?

That is because the merge statement can simply put two tables side-by-side where the records have no logical relation to each other. The "by" in a data step is used for "by-group-processing", with a merge it also names the key variables.

Re: Simplify data merge code

afiqcjohari — Mon, 10 Apr 2017 08:51:24 GMT

It will be a nice feature to have if data merge is intelligent enough to call proc sort if 'by' statement is provided.

Re: Simplify data merge code

Kurt_Bremser — Mon, 10 Apr 2017 12:05:43 GMT

@afiqcjohari wrote:
It will be a nice feature to have if data merge is intelligent enough to call proc sort if 'by' statement is provided.

That would be nuts. Making an implicit structural change (order) to a dataset will break other programs/apps that expect a specific order.

And sorting implicitly to temporary files everytime a dataset does not have the "ordered by" attribute correctly set would destroy the performance of the data step.

If you want automated sorting, use proc sql.

Re: Simplify data merge code

afiqcjohari — Tue, 11 Apr 2017 02:03:10 GMT

It's nuts if you bind yourself to the limitation of data merge of course. Hopefully SAS can take inspiration of other modern languages for this part. I still prefer data merge for the multiple outputs it can give as oppose to proc sql. Just that it's quite ugly to see proc sort prior to data merge. What to do...