topic Re: Reorder the variables after data type conversion in SAS Enterprise Guide

Reorder the variables after data type conversion

MelissaN — Mon, 16 Sep 2019 15:01:38 GMT

Hi All,

I used the data step to covert a few variables from charater to numeric.

data NEW;

SET HAVE;

Account_New=input(Account, 10.);

Bor_New=input(Bor, 3.);

drop Account Bor;

rename Account_New=Account Bor_New=Bor;

run;

After I did this, the order of variables changed. Is there any way to restore back the original order of each columns? There are about 50 columns and Account is the first one. So dropping this variable from the data step technically changed the order of all other columns.

Any help is much appreciated!

Thanks.

Re: Reorder the variables after data type conversion

PeterClemmensen — Mon, 16 Sep 2019 15:05:28 GMT

You can use a Format Statement before the set statement like this to make Account_New the first variable in the PDV.

data NEW;
	format Account_New;
	SET HAVE;
	Account_New=input(Account, 10.);
	Bor_New=input(Bor, 3.);
	drop Account Bor;
	rename Account_New=Account Bor_New=Bor;
run;

Re: Reorder the variables after data type conversion

ballardw — Mon, 16 Sep 2019 15:07:47 GMT

@MelissaN wrote:

Hi All,

I used the data step to covert a few variables from charater to numeric.

data NEW;

SET HAVE;

Account_New=input(Account, 10.);

Bor_New=input(Bor, 3.);

drop Account Bor;

rename Account_New=Account Bor_New=Bor;

run;

After I did this, the order of variables changed. Is there any way to restore back the original order of each columns? There are about 50 columns and Account is the first one. So dropping this variable from the data step technically changed the order of all other columns.

Any help is much appreciated!

Thanks.

I think you need to provide a few more details. Nothing that you show would change the order of the data set. So are you complaining that the account_new variable ends up on the "right" of the variables in a table view?

If that is the case then modify your code to:

data NEW;
   length Account_new 8;
   SET HAVE;
   Account_New=input(Account, 10.);
   Bor_New=input(Bor, 3.);
   drop Account Bor;
   rename Account_New=Account Bor_New=Bor;
run;

I generally don't worry about the order of variables inside a data set as computer programs don't care and any report that I write I can specify what goes where.

BTW I would say you are not doing yourself any help by making an account number actually numeric. Are you going to do arithmetic with it? You never know when someone is going to create "account" numbers with leading zeroes, which numeric values will not honor, or start adding characters to them.

Re: Reorder the variables after data type conversion

MelissaN — Mon, 16 Sep 2019 15:09:49 GMT

Hi draycut, thanks for your response. It actually works to make Account_New the first variable. How about the Bor columns? That colum was originally in the 30th column.

Re: Reorder the variables after data type conversion

MelissaN — Mon, 16 Sep 2019 15:15:37 GMT

Hi ballardw,
Thank you for your response.
So usually when we add new variables, they will be added to the right (like you mentioned) of the data set. I want them to be in the original order so I can union with another data set.
I'm not sure if SAS can perfrom the union if the fields of 2 data set are not matching up.
I may consider to convert the account number to charater in the other data set.
Thanks.

Re: Reorder the variables after data type conversion

PeterClemmensen — Mon, 16 Sep 2019 15:24:34 GMT

First of all, the order of variables rarely matters in SAS. If this is a one time thing, you can do the following:

1) Print the variables from the original data set in the log ordered by their initial order.

2) Copy/Paste there into a Format Statement before the Set Statement.

3) Change the two variable names to your liking.

See this small example

proc sql noprint;
	select name into :vars separated by ' '
	from dictionary.columns
	where libname='SASHELP' and memname='CLASS'
	order by varnum;
quit;

%put &vars.;

data want;
	format Name Sex Age NewHeight Weight; /* Copied from log and changed height to NewHeight */
	set sashelp.class;
	newHeight=put(Height, $8. -l);
	drop height;
	rename newHeight=Height;
run;

Re: Reorder the variables after data type conversion

Tom — Mon, 16 Sep 2019 15:26:55 GMT

SAS knows the NAMES of the variables. So if you use normal SAS code to "union" datasets

data want;
  set one two;
run;

then the position of the variables in the table is not important.

In PROC SQL you can use the CORRESPONDING (abbreviation CORR) keyword to make sure that it match variables by name.

proc sql ;
create table want as 
select * from one
union corr
select * from two
;
quit;

But I definitely prefer that dataset have the variables defined in a rational order. There is nothing worse then trying to browse a dataset to get a sense of what it looks like and have the whole screen filled with some blank $200 character field that someone put at the beginning of the data vector.

You can query the metadata of the dataset to get the list of variable names in order and use that to force the new dataset to create the variables in the same order. You could use PROC SQL and a select statement. Or the FORMAT statement (without any actual formats) in a data step.

proc contents data=have out=contents noprint; run;
proc sql noprint;
 select name into :varlist separated by ' ' 
 from contents 
 order by varnum
 ;
quit;

data want ;
  format &varlist;
  set have(rename=(acct=X_acct BOR=X_bor));
  acct = put(x_acct,z12.);
  bor = input(x_bor,comma32.);
  drop x_: ;
run;

Re: Reorder the variables after data type conversion

MelissaN — Mon, 16 Sep 2019 15:31:35 GMT

Thank you Tom. I will try this out.

Re: Reorder the variables after data type conversion

Astounding — Mon, 16 Sep 2019 18:52:28 GMT

Here's a trick you can use. It relies on you knowing that ACCOUNT is currently the first variable.

data want;
   if 5=4 then do;
      set have (keep=account);
      length Account_new 8;
      set have (keep=account--bor);
      length Bor_new 8;
   end;


   set have;
   Account_New=input(Account, 10.);
   Bor_New=input(Bor, 3.);
   drop Account Bor;
   rename Account_New=Account Bor_New=Bor;
run;

The top block of statements gets all the variables defined in the proper order.

Re: Reorder the variables after data type conversion

TomKari — Mon, 16 Sep 2019 21:01:29 GMT

Just when you think you've seen them all! I love it!!

Tom