topic Re: A data step or macro which is efficient?Give reasons in SAS Data Management

A data step or macro which is efficient?Give reasons

venkatnaveen — Sun, 24 Aug 2014 17:43:14 GMT

EfEfficiency:

If the dataset has one million of observations, which one of the following program is more efficient in terms of reducing the cpu time.

Partial dataset:

Dataset:

Var3 Seq1 var1 var2

US 100 10 1

EU 200 20 2

IND 300 30 3

Program No.1

Data US IND EU;

Set dataset;

If var3=‘US’ then output US;

If var3=’EU’ then output EU;

If var3=’IND’ then output IND;

Run;

Program no. 2

%macro report(country);

Data &country;

Set dataset;

If var3= ‘’&country ‘’ then output &country;

Run;

%mend report;

%report(US)

%report(EU)

%report(IND)

Re: A data step or macro which is efficient?Give reasons

Peter_C — Sun, 24 Aug 2014 17:45:41 GMT

I expect program 1 would be faster because it reads the input only once.

Both programs write the same three dataset.

Re: A data step or macro which is efficient?Give reasons

SASKiwi — Sun, 24 Aug 2014 19:44:15 GMT

The following logic is quicker also. And if you put the IF statements in order of most frequent then that is faster again (US has more rows than EU and EU has more rows than IND).

If var3=‘US’ then output US;

else If var3=’EU’ then output EU;

else If var3=’IND’ then output IND;

Re: A data step or macro which is efficient?Give reasons

LinusH — Mon, 25 Aug 2014 14:11:41 GMT

Program no 2 shouldn't be allowed, anywhere! A single if with an output... Always use WHERE in such situations.

Agree with SASkiwi, based on the limited information in the post.

Is this a real use case, or just an educational question?

If the data set (or DBMS table) has many more values for var3 than the mentioned, a macro approach with WHERE could be more efficient, given that var3 is indexed, or the table is partitioned (i.e. Oracle) or clustered (SPDS) by var3.