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.
I don't think you can prevent it, but you can correct it.
Sort the out= data set:
by varnum;
proc contents data=sashelp.class out=test noprint;
run;
proc sort data=test;
by Varnum;
run;
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.
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
proc sql;
create table myvars as
select
*
from
dictionary.columns
where
libname = "SASHELP"
and memname = "CARS"
;
quit;
@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..
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.
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;
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.
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,
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.