BookmarkSubscribeRSS Feed
beacon
Obsidian | Level 7

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.

10 REPLIES 10
Astounding
PROC Star

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

 

Sort the out= data set:

 

by varnum;

PeterClemmensen
Tourmaline | Level 20
proc contents data=sashelp.class out=test noprint;
run;

proc sort data=test;
	by Varnum;
run;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
SAS Super FREQ

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
Super User

@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
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

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
PROC Star

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
Obsidian | Level 7

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 6003 views
  • 1 like
  • 6 in conversation