A SAS patch changed the way PROC SQL worked and changed the way I

Discussion in 'SAS (Statistical Analysis Software)' started by Guido T, Dec 5, 2006.

  1. Guido T

    Guido T Guest

    Hi SAS-L,

    I had some PROC SQL code that I used to count the number of elements
    in a group to be later used to create a format. Here's a simplified
    version of my code :-

    proc sql;
    create table testout as
    select
    count(*) as l
    , s as x
    , s as y
    from test
    group by s
    ;
    quit;

    The idea was to get the count of number of elements in the group S
    into L and populated X and Y with the group S. The way I thought PROC
    SQL would work was to group the data by S and then populate the output
    dataset. This worked for a couple of years in a macro that wasn't
    changed.

    When I recently had to re-run the code on some old data I got a lot of
    errors from PROC FORMAT about overlapping ranges etc. That data
    hadn't changed, my code hadn't changed, the macro hadn't changed, so
    what had happened?

    When I ran the code on my PC (using an "old" version of SAS 8.2)
    everything was OK, but when I ran it on the production Unix server the
    code crashed.

    Log from my PC:

    1 data test;
    2 s = 1;
    3 do p =1 to 10;
    4 output;
    5 end;
    6 run;

    NOTE: The data set WORK.TEST has 10 observations and 2 variables.
    NOTE: DATA statement used:
    real time 0.45 seconds
    cpu time 0.04 seconds
    7
    8 proc sql feedback _method;
    9 create table testout as
    10 select
    11 count(*) as l
    12 , s as x
    13 , s as y
    14 from test
    15 group by s
    16 ;
    NOTE: Statement transforms to:

    select COUNT(*) as l, TEST.s as x, TEST.s as y
    from WORK.TEST
    group by TEST.s;

    NOTE: SQL execution methods chosen are:

    sqxcrta
    sqxsumg
    sqxsort
    sqxsrc( WORK.TEST )
    NOTE: Table WORK.TESTOUT created, with 1 rows and 3 columns.

    17 quit;
    NOTE: PROCEDURE SQL used:
    real time 0.26 seconds
    cpu time 0.09 seconds

    One record is output to TESTOUT (as I expected) and the transformed
    code looks like what I would have expected as well, just making the
    references more explicit.

    When the same code is run on Unix (Still SAS 8.2, but with more recent
    hotfixes) the following is in the log :

    8 proc sql feedback _method;
    9 create table testout as
    10 select
    11 count(*) as l
    12 , s as x
    13 , s as y
    14 from test
    15 group by s
    16 ;
    NOTE: Statement transforms to:

    select COUNT(*) as l, TEST.S as x, TEST.S as y
    from WORK.TEST
    group by x;

    NOTE: The query requires remerging summary statistics back with the
    original data.

    NOTE: SQL execution methods chosen are:

    sqxcrta
    sqxsumg
    sqxsort
    sqxsrc( WORK.TEST )
    NOTE: Table WORK.TESTOUT created, with 10 rows and 3 columns.

    TEN records in TESTOUT and the transformed code is now being grouped
    by X instead of TEST.S. I didn't say group by X, I didn't think I
    could group by a plain X, because it wasn't in the input dataset. So
    grouping by X also gives the new NOTE in the log " NOTE: The query
    requires remerging summary statistics back with the original data."
    which would reasonable if I was grouping by X, but I wasn't.

    I tried SAS 9.1.3 SP4 on Windows XP and got slightly different results.

    23 proc sql feedback;
    24 create table testout as
    25 select
    26 count(*) as l
    27 , s as x
    28 , s as y
    29 from test
    30 group by s
    31 ;
    NOTE: Statement transforms to:

    select COUNT(*) as l, TEST.s as x, TEST.s as y
    from WORK.TEST
    group by TEST.x;

    NOTE: The query requires remerging summary statistics back with the
    original data.
    NOTE: Table WORK.TESTOUT created, with 10 rows and 3 columns.

    Still TEN records, but now it is grouping by TEST.X. There isn't a
    TEST.X, Only TEST.S and TEST.P.

    There are lots of ways to get PROC SQL to return the desired result,
    but some hotfix (I don't know which one) changed the way PROC SQL
    worked and also changed my view on how PROC SQL works.

    ++ Guido
     
    Guido T, Dec 5, 2006
    #1
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.