DATA Step, Macro, Functions and more

How to prevent sort in PROC CONTENTS?

Reply
Occasional Contributor
Posts: 19

How to prevent sort in PROC CONTENTS?

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.

Super User
Posts: 6,939

Re: How to prevent sort in PROC CONTENTS?

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

 

Sort the out= data set:

 

by varnum;

PROC Star
Posts: 1,411

Re: How to prevent sort in PROC CONTENTS?

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

proc sort data=test;
	by Varnum;
run;
Super User
Super User
Posts: 9,866

Re: How to prevent sort in PROC CONTENTS?

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.

SAS Super FREQ
Posts: 831

Re: How to prevent sort in PROC CONTENTS?

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;
Super User
Posts: 13,950

Re: How to prevent sort in PROC CONTENTS?


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

Occasional Contributor
Posts: 19

Re: How to prevent sort in PROC CONTENTS?

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.

Super User
Posts: 13,950

Re: How to prevent sort in PROC CONTENTS?

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;
Occasional Contributor
Posts: 19

Re: How to prevent sort in PROC CONTENTS?

[ Edited ]

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.

Super User
Posts: 6,939

Re: How to prevent sort in PROC CONTENTS?

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,

 

 

Occasional Contributor
Posts: 19

Re: How to prevent sort in PROC CONTENTS?

Posted in reply to Astounding

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.

Ask a Question
Discussion stats
  • 10 replies
  • 336 views
  • 1 like
  • 6 in conversation