topic Re: Reordering columns in a DATA STEP is not working in SAS Programming

Reordering columns in a DATA STEP is not working

u787bruw — Fri, 24 Mar 2023 14:49:31 GMT

I am trying to reorder columns in SAS and it is not reordering the columns. I have tried doing this with the SET WORK.CLR_CALL_AGENTS_GS right under the DATA WORK... statement and the columns still are not reordering.

DATA WORK.CLR_CALL_AGENTS_GS_1;
	/* Change column lengths for character columns */
	LENGTH	AGENT_ID
			AGENT_TO_AGENT_CHAT_DISABLED
			EMAIL_ADDRESS
			ENABLED
			FULL_NAME
			LOGIN_ID
			USER_RIGHTS
			CHAR_DIRECT_AGENT_ACCESS
			$100;

	/* Rename columns */
	RENAME 	'AGENT_ID'N 						= 'AGENT-ID'N
			'AGENT_TO_AGENT_CHAT_DISABLED'N		= 'AGENT-TO-AGENT-CHAT-DISABLED'N
			'CHAR_DIRECT_AGENT_ACCESS'N 		= 'DIRECT-AGENT-ACCESS'N
			'EMAIL_ADDRESS'N 					= 'EMAIL-ADDRESS'N
			'FULL_NAME'N 						= 'FULL-NAME'N
			'LOGIN_ID'N 						= 'LOGIN-ID'N
			'USER_RIGHTS'N 						= 'USER-RIGHTS'N;\

    /* Reorder columns */
    RETAIN 'AGENT-ID'N 'AGENT-TO-AGENT-CHAT-DISABLED'N 'DIRECT-AGENT-ACCESS'N 'EMAIL-ADDRESS'N 'ENABLED'N 'FULL-NAME'N 'LOGIN-ID'N 'USER-RIGHTS'N;

	/* Create dataset */
	SET WORK.CLR_CALL_AGENTS_GS;

	/* Modify the 'DIRECT-AGENT-ACCESS' values */
	CHAR_DIRECT_AGENT_ACCESS = PUT(DIRECT_AGENT_ACCESS, 5.);
	DROP DIRECT_AGENT_ACCESS;

	IF CHAR_DIRECT_AGENT_ACCESS = 0 THEN CHAR_DIRECT_AGENT_ACCESS = 'no';
	ELSE IF CHAR_DIRECT_AGENT_ACCESS = 1 THEN CHAR_DIRECT_AGENT_ACCESS = 'yes';
RUN;

The only way I can get the columns to reorder is if I do another DATA STEP statement:

/* Create a new data set for reordering columns */
DATA WORK.CLR_CALL_AGENTS_GS_2;
	RETAIN 'AGENT-ID'N 'AGENT-TO-AGENT-CHAT-DISABLED'N 'DIRECT-AGENT-ACCESS'N 'EMAIL-ADDRESS'N 'ENABLED'N 'FULL-NAME'N 'LOGIN-ID'N 'USER-RIGHTS'N;

	SET WORK.CLR_CALL_AGENTS_GS_1;
RUN;

Re: Reordering columns in a DATA STEP is not working

PaigeMiller — Fri, 24 Mar 2023 15:01:28 GMT

No need for two data steps. Just do it in one data step, use the RETAIN as the first statement in the data step.

Also, renaming one variable to have the name of another variable

'CHAR_DIRECT_AGENT_ACCESS'N 		= 'DIRECT-AGENT-ACCESS'N

doesn't seem to be a good thing to do just to re-order the variables (unless you know that the variable was improperly named).

Re: Reordering columns in a DATA STEP is not working

u787bruw — Fri, 24 Mar 2023 15:10:01 GMT

The reason I renamed it is because the original column is a "DIRECT_AGENT_ACCESS" is a numeric column. I needed to turn the 0's and 1's in that column into character values. I found the solution I had which was:

Creating a new character-type column, "CHAR_DIRECT_AGENT_ACCESS", with the data from "DIRECT_AGENT_ACCESS"
Drop the original "DIRECT_AGENT_ACCESS" numeric column
Rename the new character-type column, "CHAR_DIRECT_AGENT_ACCESS", to "DIRECT-AGENT-ACCESS".

So yes, I didn't do it for reordering purposes, I did it because it was technically improperly named.

But, I did put the 'RETAIN' statement as the first statement, but I achieve nothing from it unfortunately.

Re: Reordering columns in a DATA STEP is not working

Quentin — Fri, 24 Mar 2023 15:30:25 GMT

@u787bruw wrote:

But, I did put the 'RETAIN' statement as the first statement, but I achieve nothing from it unfortunately.

The RETAIN statement needs to be the very first statement, before the LENGTH statement.

Re: Reordering columns in a DATA STEP is not working

PaigeMiller — Fri, 24 Mar 2023 15:35:25 GMT

@u787bruw wrote:

But, I did put the 'RETAIN' statement as the first statement, but I achieve nothing from it unfortunately.

When something isn't working, you can't say it didn't work and then provide no other information. In that case, we can't help you. When something isn't working, show us the code and show us the log. Then we can help further.

Re: Reordering columns in a DATA STEP is not working

u787bruw — Fri, 24 Mar 2023 15:36:13 GMT

DATA WORK.CLR_CALL_AGENTS_GS_1;
	/* Reorder columnns */
	RETAIN 	'AGENT-ID'N
			'AGENT-TO-AGENT-CHAT-DISABLED'N
			'DIRECT-AGENT-ACCESS'N
			'EMAIL-ADDRESS'N
			'ENABLED'N
			'FULL-NAME'N
			'LOGIN-ID'N
			'USER-RIGHTS'N;

	/* Change column lengths for character columns */
	LENGTH	AGENT_ID
			AGENT_TO_AGENT_CHAT_DISABLED
			EMAIL_ADDRESS
			ENABLED
			FULL_NAME
			LOGIN_ID
			USER_RIGHTS
			CHAR_DIRECT_AGENT_ACCESS
			$100;

	/* Rename columns */
	RENAME 	'AGENT_ID'N 						= 'AGENT-ID'N
			'AGENT_TO_AGENT_CHAT_DISABLED'N		= 'AGENT-TO-AGENT-CHAT-DISABLED'N
			'CHAR_DIRECT_AGENT_ACCESS'N 		= 'DIRECT-AGENT-ACCESS'N
			'EMAIL_ADDRESS'N 					= 'EMAIL-ADDRESS'N
			'FULL_NAME'N 						= 'FULL-NAME'N
			'LOGIN_ID'N 						= 'LOGIN-ID'N
			'USER_RIGHTS'N 						= 'USER-RIGHTS'N;


	/* Create dataset */
	SET WORK.CLR_CALL_AGENTS_GS;

	/* Modify the 'DIRECT-AGENT-ACCESS' values */
	CHAR_DIRECT_AGENT_ACCESS = PUT(DIRECT_AGENT_ACCESS, 5.);
	DROP DIRECT_AGENT_ACCESS;

	IF CHAR_DIRECT_AGENT_ACCESS = 0 THEN CHAR_DIRECT_AGENT_ACCESS = 'no';
	ELSE IF CHAR_DIRECT_AGENT_ACCESS = 1 THEN CHAR_DIRECT_AGENT_ACCESS = 'yes';
RUN;

I did it, but not the result I am looking for. For reference, 'DIRECT-AGENT-ACCESS' is the very last column when the dataset gets outputted -- it should be the 3rd. I think it is because I did the column, and then remade it.

Re: Reordering columns in a DATA STEP is not working

Quentin — Fri, 24 Mar 2023 15:42:05 GMT

@u787bruw wrote:

I did it, but not the result I am looking for. For reference, 'DIRECT-AGENT-ACCESS' is the very last column when the dataset gets outputted -- it should be the 3rd. I think it is because I did the column, and then remade it.

You're right. Since you're renaming the variable, you need CHAR_DIRECT_AGENT_ACCESS to be in the correct position in the Program Data Vector. Try:

	RETAIN 	'AGENT-ID'N
			'AGENT-TO-AGENT-CHAT-DISABLED'N
			CHAR_DIRECT_AGENT_ACCESS
			'EMAIL-ADDRESS'N
			'ENABLED'N
			'FULL-NAME'N
			'LOGIN-ID'N
			'USER-RIGHTS'N;

Re: Reordering columns in a DATA STEP is not working

u787bruw — Fri, 24 Mar 2023 15:51:02 GMT

/* Create a new SAS dataset from WORK library */
DATA WORK.CLR_CALL_AGENTS_GS_1;
	/* Reorder columns */
	RETAIN 	'AGENT-ID'N
			'AGENT-TO-AGENT-CHAT-DISABLED'N
			'CHAR_DIRECT_AGENT_ACCESS'N
			'EMAIL-ADDRESS'N
			'ENABLED'N
			'FULL-NAME'N
			'LOGIN-ID'N
			'USER-RIGHTS'N;

	/* Change column lengths for character columns */
	LENGTH	AGENT_ID
			AGENT_TO_AGENT_CHAT_DISABLED
			CHAR_DIRECT_AGENT_ACCESS
			EMAIL_ADDRESS
			ENABLED
			FULL_NAME
			LOGIN_ID
			USER_RIGHTS
			$100;

	/* Rename columns */
	RENAME 	'AGENT_ID'N 						= 'AGENT-ID'N
			'AGENT_TO_AGENT_CHAT_DISABLED'N		= 'AGENT-TO-AGENT-CHAT-DISABLED'N
			'CHAR_DIRECT_AGENT_ACCESS'N 		= 'DIRECT-AGENT-ACCESS'N
			'EMAIL_ADDRESS'N 					= 'EMAIL-ADDRESS'N
			'FULL_NAME'N 						= 'FULL-NAME'N
			'LOGIN_ID'N 						= 'LOGIN-ID'N
			'USER_RIGHTS'N 						= 'USER-RIGHTS'N;

	/* Create dataset */
	SET WORK.CLR_CALL_AGENTS_GS;

	/* Modify the 'DIRECT-AGENT-ACCESS' values */
	CHAR_DIRECT_AGENT_ACCESS = PUT(DIRECT_AGENT_ACCESS, 5.);
	DROP DIRECT_AGENT_ACCESS;

	IF CHAR_DIRECT_AGENT_ACCESS = 0 THEN CHAR_DIRECT_AGENT_ACCESS = 'no';
	ELSE IF CHAR_DIRECT_AGENT_ACCESS = 1 THEN CHAR_DIRECT_AGENT_ACCESS = 'yes';
RUN;

Funny enough, it is now the very first column in the order. Here is the log below. ***It contains some commented code that I omitted in the example above.

MLOGIC(_EG_CONDITIONAL_DROPDS):  Ending execution.
MPRINT(LOCK_TABLE):  ;
MPRINT(LOCK_TABLE):   ***ATTEMPT TO LOCK THE TABLE. SAS STORES A LOCK STATUS CODE IN SYSLCKRC;
SYMBOLGEN:  Macro variable LIB_NAME resolves to CLR_STGA
SYMBOLGEN:  Macro variable TABLE_NAME resolves to CLR_CALL_AGENTS_GS
MPRINT(LOCK_TABLE):   LOCK CLR_STGA.CLR_CALL_AGENTS_GS;
NOTE: CLR_STGA.CLR_CALL_AGENTS_GS.DATA is now locked for exclusive access by you.
MPRINT(LOCK_TABLE):   ***IF THE SYSLCKRC = 0, THEN THE LOCK WAS SUCCESSFUL. END THE MACRO;
SYMBOLGEN:  Macro variable SYSLCKRC resolves to 0
MLOGIC(LOCK_TABLE):  %IF condition &SYSLCKRC = 0 is TRUE
MLOGIC(LOCK_TABLE):  Ending execution.
48         
49         /* Create a new SAS dataset from WORK library */
50         DATA WORK.CLR_CALL_AGENTS_GS_1;
51         	/* Reorder columnns */
52         	RETAIN 	'AGENT-ID'N
53         			'AGENT-TO-AGENT-CHAT-DISABLED'N
54         			'CHAR_DIRECT_AGENT_ACCESS'N
55         			'EMAIL-ADDRESS'N
56         			'ENABLED'N
57         			'FULL-NAME'N
58         			'LOGIN-ID'N
59         			'USER-RIGHTS'N;
60         
61         	/* Create new columns for first-name and last-name */
62         	/*	'LAST_NAME'N		= SCAN('FULL_NAME'N, 1, ",");*/
63         	/*	'FIRST_NAME'N 		= SUBSTR(SCAN('FULL_NAME'N, 2, ","), 2);*/
64         
65         	/* Change column lengths for character columns */
66         	LENGTH	AGENT_ID
67         			AGENT_TO_AGENT_CHAT_DISABLED
68         			EMAIL_ADDRESS
69         			ENABLED
70         			FULL_NAME
71         			LOGIN_ID
72         			USER_RIGHTS
73         			CHAR_DIRECT_AGENT_ACCESS
74         			$100;
75         
76         	/* Rename columns */
77         	RENAME 	'AGENT_ID'N 						= 'AGENT-ID'N
78         			'AGENT_TO_AGENT_CHAT_DISABLED'N		= 'AGENT-TO-AGENT-CHAT-DISABLED'N
79         			'CHAR_DIRECT_AGENT_ACCESS'N 		= 'DIRECT-AGENT-ACCESS'N
80         			'EMAIL_ADDRESS'N 					= 'EMAIL-ADDRESS'N
83         			'FULL_NAME'N 						= 'FULL-NAME'N
84         			'LOGIN_ID'N 						= 'LOGIN-ID'N
85         			'USER_RIGHTS'N 						= 'USER-RIGHTS'N;
86         
87         	/* Create dataset */
88         	SET WORK.CLR_CALL_AGENTS_GS;
89         
90         	/* Modify the 'DIRECT-AGENT-ACCESS' values */
7                                                          The SAS System                               08:40 Friday, March 24, 2023

91         	CHAR_DIRECT_AGENT_ACCESS = PUT(DIRECT_AGENT_ACCESS, 5.);
92         	DROP DIRECT_AGENT_ACCESS;
93         
94         	IF CHAR_DIRECT_AGENT_ACCESS = 0 THEN CHAR_DIRECT_AGENT_ACCESS = 'no';
95         	ELSE IF CHAR_DIRECT_AGENT_ACCESS = 1 THEN CHAR_DIRECT_AGENT_ACCESS = 'yes';
96         RUN;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
      94:5    95:10   
NOTE: Variable 'AGENT-ID'n is uninitialized.
NOTE: Variable 'AGENT-TO-AGENT-CHAT-DISABLED'n is uninitialized.
NOTE: Variable 'EMAIL-ADDRESS'n is uninitialized.
NOTE: Variable 'FULL-NAME'n is uninitialized.
NOTE: Variable 'LOGIN-ID'n is uninitialized.
NOTE: Variable 'USER-RIGHTS'n is uninitialized.
WARNING: Multiple lengths were specified for the variable AGENT_ID by input data set(s). This can cause truncation of data.
WARNING: Multiple lengths were specified for the variable EMAIL_ADDRESS by input data set(s). This can cause truncation of data.
WARNING: Multiple lengths were specified for the variable ENABLED by input data set(s). This can cause truncation of data.
WARNING: Multiple lengths were specified for the variable FULL_NAME by input data set(s). This can cause truncation of data.
WARNING: Multiple lengths were specified for the variable LOGIN_ID by input data set(s). This can cause truncation of data.
WARNING: Multiple lengths were specified for the variable USER_RIGHTS by input data set(s). This can cause truncation of data.
NOTE: There were 47 observations read from the data set WORK.CLR_CALL_AGENTS_GS.
NOTE: The data set WORK.CLR_CALL_AGENTS_GS_1 has 47 observations and 8 variables.
NOTE: Compressing data set WORK.CLR_CALL_AGENTS_GS_1 increased size by 100.00 percent. 
      Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

Re: Reordering columns in a DATA STEP is not working

Quentin — Fri, 24 Mar 2023 16:05:40 GMT

See all those uninitialized notes in the log?

The RETAIN statement needs to list the variable names that exist in the input dataset WORK.CLR_CALL_AGENTS_GS, (before you have renamed them).

You should also resolve the notes about length conflicts and the automatic character to numeric conversion, but those are separate issues.

Re: Reordering columns in a DATA STEP is not working

u787bruw — Fri, 24 Mar 2023 16:06:38 GMT

Nevermind. I have found the issue. I needed to change the column names in the 'RETAIN' statement:

/* Create a new SAS dataset from WORK library */
DATA WORK.CLR_CALL_AGENTS_GS_1;
	/* Reorder columns */
	RETAIN 	'AGENT-ID'N
			'AGENT-TO-AGENT-CHAT-DISABLED'N
			'CHAR_DIRECT_AGENT_ACCESS'N
			'EMAIL-ADDRESS'N
			'ENABLED'N
			'FULL-NAME'N
			'LOGIN-ID'N
			'USER-RIGHTS'N;

As you can see, there is '-' instead '_' for most of the column names in the 'RETAIN' statement. I switched those to '_' and the reordering worked!

Re: Reordering columns in a DATA STEP is not working

u787bruw — Fri, 24 Mar 2023 16:07:20 GMT

Hahahahaha I just noticed it and fixed and posted about it. Thank you for the help!