BookmarkSubscribeRSS Feed
venkatnaveen
Obsidian | Level 7

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)

3 REPLIES 3
Peter_C
Rhodochrosite | Level 12

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

Both programs write the same three dataset.

SASKiwi
PROC Star

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;


LinusH
Tourmaline | Level 20

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.

Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 1236 views
  • 3 likes
  • 4 in conversation