PROC SQL with GROUP command extremely slow. Why? Workaround possible?

Question

I have a MACRO which takes a data set D and essentially outputs k disjoint datasets, D_1,...,D_k. The value k is not fixed and depends on properties of the data that are not known in advance. We can assume that k is not larger than 10, though.

The dataset D contains the variables x and y, and I want to overlay the line/scatter plots of x and y for each of D_i over each other. In my particular case x is time, and I want to see the output y for each D_i and compare them to each other.

Hopefully that was clear.

How can I do this? I don't know k in advance, so I need some sort of %do loop. But it doesn't seem that I can put a do loop inside "proc sgplot".

I might be able to make a macro that includes a very long series of commands, but I'm not sure.

How can I overlay these plots in SAS?

EDIT: I am including for reference why I am trying to avoid doing a PROC SGPLOT with the GROUP clause. I tried the following code and it is taking over 30 minutes to compute (I canceled the calculation after this, so I don't know how long it will actually take). PROC SQL runs quite quickly, the program is stuck on PROC SGPLOT.

    proc sql;
    create table dataset as select    
        date, product_code, sum(num_of_records) as total_rec
        from &filename
            group by product_code, data
                order by product_code, date
    ;
quit;

PROC SGPLOT Data = dataset;
    scatter x = date   y = total_rec/group=product_code;
    title "Total records by product code";
run;

The number of observations in the file is 76,000,000.

Can you make an example &filename dataset - to show the frequency of product_Code and such? Just data mydata; do product_code = 1 to 10000; do date = 1 to 7600; (some way to generate sensible x y); output;end end; run; or something along those lines. Something that matches approximately how many product codes and how many rows per product code. — Joe
I get stuck on a simple case there are two product codes and where half of the data has one product code, the other half has the other product code. Is this enough? — Lepidopterist
If it reproduces your issue, that is perfectly fine. Something that takes 30-60 minutes or whatever for you to run that you want to make faster. — Joe
Ok, I will do this. I will only have time in an hour, but will get to it. I appreciate your help! — Lepidopterist

Joe Joe · Accepted Answer · 2017-02-10T15:09:58

What you should do is either change your macro to produce one dataset with a variable d_i (or whatever you can logically name it) which identifies which dataset it would've gone to (or identifies it with whatever determines what dataset it would've gone to), or post-macro combine the datasets.

Then, you can use group to overlay your plots. So for example:

data my_data;
  call streaminit(7);
  do d_i = 1 to 5;
    y = 10;    
    x = 0;
    output;
    do x = 1 to 10;
      y + round(rand('Uniform')*3,.1)-1.5;
      output;
    end;
  end;
run;

proc sgplot data=my_data;
  series x=x y=y/group=d_i;
run;

PROC SQL with GROUP command extremely slow. Why? Workaround possible?

1 Answers