BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
u787bruw
Calcite | Level 5

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Quentin
PROC Star

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.

 

Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.

View solution in original post

10 REPLIES 10
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
u787bruw
Calcite | Level 5

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:

  1. Creating a new character-type column, "CHAR_DIRECT_AGENT_ACCESS", with the data from "DIRECT_AGENT_ACCESS"
  2. Drop the original "DIRECT_AGENT_ACCESS" numeric column
  3. 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.

Quentin
PROC Star

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

 

 

Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.
u787bruw
Calcite | Level 5
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.

Quentin
PROC Star

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

 

Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.
u787bruw
Calcite | Level 5

 

/* 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

 

 

 

Quentin
PROC Star

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.

 

Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.
u787bruw
Calcite | Level 5

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

u787bruw
Calcite | Level 5

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!

PaigeMiller
Diamond | Level 26

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

--
Paige Miller

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 10 replies
  • 460 views
  • 1 like
  • 3 in conversation