How to prevent sort in PROC CONTENTS?

[beacon]

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.

[Astounding]

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

Sort the out= data set:

by varnum;

[PeterClemmensen]

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

proc sort data=test;
	by Varnum;
run;

[RW9]

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.

[BrunoMueller]

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.

See also 

http://go.documentation.sas.com/?docsetId=sqlproc&docsetTarget=n02s19q65mw08gn140bwfdh7spx7.htm&docs...

proc sql;
  create table myvars as
  select
    *
  from
    dictionary.columns
  where
    libname = "SASHELP"
    and memname = "CARS"
  ;
quit;

[ballardw]

---

@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..

[beacon]

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.

[ballardw]

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;

[beacon]

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.

[Astounding]

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,

[beacon]

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.