topic Re: proc means vs proc sql. in SAS Programming

proc means vs proc sql.

k_shide — Sun, 09 Apr 2017 12:06:19 GMT

Dear Gurus,

I have a dataset contains 21 million observations with 1500 variables.

I just want to make sum of 500 variables using two index variables. (say, product_code and date).

I still can finish the manipulation by using proc means by spending 30 minutes but just wondering

if any of you experienced proc sql with group by can be faster or not?

I read some of articles about proc means vs proc sql but not very sure for the big data with many numbers of variables.

By the way, the data set is native SAS dataset and not coming from Oracle or any other database.

Any opinion from will be appreciated.

Thank you,

Kaz

Re: proc means vs proc sql.

Patrick — Sun, 09 Apr 2017 12:29:15 GMT

Whatever procedure you're using it will have to read the data and to sort the data for summing by groups.

Both Proc SQL and Proc Means are thread-enabled.

http://support.sas.com/documentation/cdl/en/lrcon/69852/HTML/default/viewer.htm#n0czb9vxe72693n1lom0qmns6zlj.htm

I don't expect Proc SQL to outperform Proc Means for this task.

What you probably want to do is to ensure that multi-threading can get used where possible (=options set to allow for multi-threading).

I would expect this process to be I/O bound so what certainly will impact on performance is how your SAS WORK and UTILLOC area (used for sorting) are set up (but that's something only an admin can change).

Re: proc means vs proc sql.

k_shide — Sun, 09 Apr 2017 12:36:33 GMT

Patrick,

thanks very much for your quick reply.

I read through and found the right key word for the option.

also thanks for not wasting time for replacing codes by proc sql still taking around 30 minutes.

Kaz

Re: proc means vs proc sql.

Patrick — Sun, 09 Apr 2017 12:51:43 GMT

30 minutes doesn't feel that bad for the volumes you're dealing with: 8 Bytes for a numeric variable * 500 variables * 21M rows add up to more than 78GB.

I'd expect the main bottleneck to be I/O which is nothing you can do much about it except to reduce volumes as fast as you can and to minimize passes through the data while the volumes are high.

Re: proc means vs proc sql.

k_shide — Sun, 09 Apr 2017 13:09:52 GMT

Yes. It's not bad at all.

Recently many guys says Python Python Python everyday and off course I used it for certain purposes but

for big data manipulations like this, I think SAS still holds significatn advantage to other languages.

Together with macro facility, it allows so much freedom.

Thanks anyway,

Kaz

Re: proc means vs proc sql.

Ksharp — Mon, 10 Apr 2017 13:08:45 GMT

Add some options to fast your code.

option bufno=100 bufsize=128k cpucount=12 threads;

You also could try PROC TABULATE.

@Chris_NewZ claim PROC TABULATE is faster than MEANS or SQL at somewhere.

ods select none;
proc tabulate data=sashelp.class out=want ;
var age weight height;
table (age weight height)*sum;
run;
ods select all;

Re: proc means vs proc sql.

k_shide — Mon, 10 Apr 2017 23:35:20 GMT

Thank you for the parameter setting and

I totally forgot tabulate procedure which more flexible functions than means.

Kaz