SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

reorder variables

Reply
Occasional Contributor
Posts: 7

reorder variables

Hi All,

 

I got a question here:

suppose I transfer a character variable to numeric variable, and I'll get a new numeric variable, and this new variable will be output at the end of my dataset.  I know I can use retain statement to reorder variables. But if I have lots of variables, I need to type all variable names, really inconverient. Any other way to directly replace character variable?

for example:

I have variables: A B C D E  

then Character to numeric and drop character: B C D E F(numeric, transferred from A)

then retain statement to reorder: F B C D E

 

Is ther a way to replace A with F directly?

 

many thanks,

Amy

Super User
Posts: 9,671

Re: reorder variables

Unfortunately, You have to type all these variable name to reorder them. But you can let SAS do it for you .

 

data class;
 set sashelp.class;
run;




proc transpose data=class(obs=0) out=name;
 var _all_;
run;
data name;
 length _name_ $ 80;
 set name;
 if upcase(_name_)='AGE' then _name_='new_age';
run;
proc sql noprint;
 select _name_ into : list separated by ' '
  from name;
quit;

data want;
 set class;
 new_age=put(age,best8.);
 drop age;
run;
data want;
 retain &list;
 set want;
run;
Super User
Super User
Posts: 7,395

Re: reorder variables

Hi,

 

You could use SQL:

proc sql;
  create table WANT as
  select  input(A,best.) as A,
          B,C,D,E,F
  from    HAVE;
quit;

I would be careful doing an automated convert every variable to number - you could get into all kinds of problems.

 

At the end of the day however, position of variable in the dataset does not matter in the context of programming, only for output, so the only place you need to fix this is for output - e.g. reports - rtf, pdf - or data transfer.  At that point you would fix it anyways?  So whats the need for this?

Occasional Contributor
Posts: 7

Re: reorder variables

Thanks for the reply.
I have many variables need to compare simultaneously, but I don't want to rearrange variable every time.
Super User
Super User
Posts: 7,395

Re: reorder variables

Can I suggest an example of what you mean, what comparison are you doing?  I thought this was about order of variables.  How will you know what variables are to be changed, do they all have a prefix?

Also, why do you have "lots of variables", which immediately throws up questions to my mind, as it is far easier from a programming point of view to be talking about lots of observations rather than variables.  (Test data as a datastep and required outpu is also useful - note that the data you work with does not need to look like any output report or transfer file).

Trusted Advisor
Posts: 1,115

Re: reorder variables

Here's another option:

/* Create test data */

data have;
array x $3 A B C D E (5*'123');
run;

/* Replace character variable C with a numeric variable F in the same position in the PDV */

data want;
if 0 then set have(keep=A--B); /* A--C works as well */
length F 8;
set have;
F=input(C, 32.);
drop C;
run;

/* View the result */

proc contents data=want varnum;
run;

The first SET statement creates the first part of the PDV, although it is not executed (as the IF condition is never satisfied), and thus ensures the desired position of the new variable. Thanks to the variable list there is no need to type many variable names.

 

If you want to replace the first variable (A) as in your example, you don't even need the additional SET statement. The LENGTH statement before the (other) SET statement suffices to create F in the first position of the PDV.

Ask a Question
Discussion stats
  • 5 replies
  • 352 views
  • 1 like
  • 4 in conversation