topic Re: How to prevent sort in PROC CONTENTS? in SAS Programming

How to prevent sort in PROC CONTENTS?

beacon — Wed, 10 Jan 2018 16:18:47 GMT

Hi,

Is it possible to prevent PROC CONTENTS from sorting the OUT= output? I need to process some column names in a data set, but I need them to remain in their original order.

I've read through numerous documents for different versions of SAS, but I'm not seeing this option. Just want to make sure I haven't overlooked it.

Thanks.

Re: How to prevent sort in PROC CONTENTS?

Astounding — Wed, 10 Jan 2018 16:27:30 GMT

I don't think you can prevent it, but you can correct it.

Sort the out= data set:

by varnum;

Re: How to prevent sort in PROC CONTENTS?

PeterClemmensen — Wed, 10 Jan 2018 16:28:53 GMT

proc contents data=sashelp.class out=test noprint;
run;

proc sort data=test;
	by Varnum;
run;

Re: How to prevent sort in PROC CONTENTS?

RW9 — Wed, 10 Jan 2018 16:44:02 GMT

You do not need to use proc contents. You can access the metadata directly by using either:

sashelp.vtable and sashelp.vcolumn

Or in sql by the dictionary.tables/columns.

Re: How to prevent sort in PROC CONTENTS?

BrunoMueller — Wed, 10 Jan 2018 16:53:03 GMT

You may want to explore the DICTIONARY tables available with Proc SQL, see example below.

It gives you easy access to the column and table information using SQL.

Re: How to prevent sort in PROC CONTENTS?

ballardw — Wed, 10 Jan 2018 16:53:30 GMT

@beacon wrote:

Hi,

Is it possible to prevent PROC CONTENTS from sorting the OUT= output? I need to process some column names in a data set, but I need them to remain in their original order.

I've read through numerous documents for different versions of SAS, but I'm not seeing this option. Just want to make sure I haven't overlooked it.

Thanks.

Please provide an example, or at least a description of what you are doing, that variable order from a proc contents data set is critical.

I am having a hard time coming up with an example..

Re: How to prevent sort in PROC CONTENTS?

beacon — Wed, 10 Jan 2018 17:18:45 GMT

So, upon further testing, using some of the examples provided above, it appears as though the issue is actually with a subsequent PROC SQL where I'm trying to return DISTINCT records.

/* There are additional columns that are not listed that are in the data set 'x' */
DATA x;
    INPUT C_UV $ C_WX $ C_YZ $ C_ST $ C_QR $ B_UV $ B_WX $ B_YZ $ B_ST $ B_QR $ E_UV $ E_WX $ E_YZ $ E_ST $ E_QR $ D_UV $ D_WX $ D_YZ $ D_ST $ D_QR $ A_UV $ A_WX $ A_YZ $ A_ST $ A_QR $;
    DATALINES;
    ...

PROC CONTENTS DATA=x OUT=y VARNUM;
RUN;

PROC SQL;
    /* Get just the portion of the code after the underscore and sort by original order */
    CREATE TABLE a AS
        SELECT
            PRXCHANGE('s/^(\D{1})_(\w+)/$2/', -1, NAME) AS CODE
        FROM
            y
        WHERE
            PRXMATCH('/^\D{1}_/', NAME)
        ORDER BY
            VARNUM
        ;

    /* Get the distinct code after sort */
    CREATE TABLE b AS
        SELECT DISTINCT
            CODE
        FROM
            a
        ;
QUIT;

PROC PRINT DATA=b;
RUN;

/* Desired Output
CODE
-----------
UV
WX
YZ
ST
QR
*/

/* Actual Output

CODE
-----------
QR
ST
UV
WX
YZ
*/

I tried to pin this down and thought it was because of PROC CONTENTS, but that's because I mistakenly thought that the DISTINCT clause in PROC SQL was a standardized implementation that didn't return values in sorted order, as is apparently the case.

Thanks.

Re: How to prevent sort in PROC CONTENTS?

ballardw — Wed, 10 Jan 2018 17:24:39 GMT

if you keep VARNUM in table a you can still use it for order by in the table b creation without including in the output.

OR if you don't really need table A

PROC SQL;
    /* Get just the portion of the code after the underscore and sort by original order */
    CREATE TABLE b  AS
        SELECT
           distinct PRXCHANGE('s/^(\D{1})_(\w+)/$2/', -1, NAME) AS CODE
        FROM
            y
        WHERE
            PRXMATCH('/^\D{1}_/', NAME)
        ORDER BY
            VARNUM
        ;
quit;

Re: How to prevent sort in PROC CONTENTS?

beacon — Wed, 10 Jan 2018 17:34:11 GMT

I tried that, but I get the following warning:

WARNING: The query as specified involves ordering by an item that doesn't appear in its SELECT
         clause. Since you are ordering the output of a SELECT DISTINCT it may appear that some
         duplicates have not been eliminated.

Despite the warning, the output is incorrect. Here is the desired output and the actual output (when I try to ORDER BY with DISTINCT):

/* Desired Output
CODE
-----------
UV
WX
YZ
ST
QR
*/

/* Actual Output

CODE
-----------
UV
WX
YZ
ST
QR
UV
WX
YZ
ST
QR
UV
WX
YZ
ST
QR
UV
WX
YZ
ST
QR
UV
WX
YZ
ST
QR
*/

I tried to use an inline view to sort and limit the values returned to the main query, but inline views apparently don't allow the ORDER BY clause.

Thanks.

Re: How to prevent sort in PROC CONTENTS?

Astounding — Wed, 10 Jan 2018 18:21:26 GMT

That warning message is pretty explicit. You should be able to fix the situation by changing one line of the program from:

SELECT

Instead, make it:

SELECT VARNUM,

Re: How to prevent sort in PROC CONTENTS?

beacon — Wed, 10 Jan 2018 19:05:44 GMT

If I use the code suggested by ballardw, which I had tried, it looks like this:

PROC SQL;
    /* Get just the portion of the code after the underscore and sort by original order */
    CREATE TABLE b  AS
        SELECT DISTINCT
            PRXCHANGE('s/^(\D{1})_(\w+)/$2/', -1, NAME) AS CODE
        FROM
            y
        WHERE
            PRXMATCH('/^\D{1}_/', NAME)
        ORDER BY
            VARNUM
        ;
QUIT;

If you're suggesting that I change it to the following, it will remove the warning, but it won't produce the desired output:

PROC SQL;
    /* Get just the portion of the code after the underscore and sort by original order */
    CREATE TABLE b  AS
        SELECT DISTINCT
            PRXCHANGE('s/^(\D{1})_(\w+)/$2/', -1, NAME) AS CODE,
            VARNUM    /* Added VARNUM, per Astounding's suggestion */
        FROM
            y
        WHERE
            PRXMATCH('/^\D{1}_/', NAME)
        ORDER BY
            VARNUM
        ;
QUIT;

If you were suggesting that I use the code above, but remove DISTINCT, that also does not produce the desired output. The above code and the code above without DISTINCT both produce the following results:

/* Actual Output

CODE         VARNUM
-----------  -----------
UV           73
WX           74
YZ           75
ST           76
QR           77
UV           78
WX           79
YZ           80
ST           81
QR           82
UV           83
WX           84
YZ           85
ST           86
QR           87
UV           88
WX           89
YZ           90
ST           91
QR           92
UV           93
WX           94
YZ           95
ST           96
QR           97
*/

/* Desired Output
CODE
-----------
UV
WX
YZ
ST
QR
*/

Thanks.