topic Re: rearrae variables in SAS Programming

rearrae variables

SASPhile — Wed, 26 Aug 2009 19:55:54 GMT

I have a quick question about changing the internal order of
variables in a datset.
I have a dataset with 23 variables. But I need them to be arrang in a specific order.

for instance
provider PATIENT_ID ndc_number NDC_DESC lot_No

are to be arranged as:
ndc_number NDC_DESC PATIENT_ID lot_No provider

Re: rearrae variables

sbb — Wed, 26 Aug 2009 20:07:16 GMT

Why do you feel they need to be re-arranged, internally? You have no control now SAS stores the variables, other than when generated by-default order with say PROC PRINT and no VAR statement listed. You can influence the declared order of your SAS variables by defining a RETAIN statement (ahead of the SET, if coded) in the desired order - be careful using the RETAIN statement as has been discussed on the various SAS topic forums on the planet.

Scott Barry
SBBWorks, Inc.

Re: rearrange variables

mftuchman — Wed, 26 Aug 2009 20:32:10 GMT

Scott is 100% right with regard to sound practice. With so many SAS reporting procedures that let you order columns as needed, there is really no reason to need this. However, an audience raised in Excel is an audience that is accustomed to moving columns around. Thus, I can see where this issue originates.

Base SAS VIEWTABLE, and SAS Enterprise Guide give a default view of the data. In some cases, I can see why people might want the most interesting variables "at the left". I've been known to force an ATTRIB statement before the SET statement so that "my favorites" come up on the left in a new view.

But for the most part I agree with Scott.

data test / view=test;
attrib age label='Age' weight label='Weight' sex label='Sex' height label='Height' name label='Name';
set sashelp.class;
run;

If you now click on WORK.TEST in the explorer, the data will appear in the order listed on the ATTRIB statement. But really, other ways are better.

Re: rearrae variables

mftuchman — Wed, 26 Aug 2009 20:33:35 GMT

These variable names seem *awfully* familiar. You work in my office? 🙂

Re: rearrae variables

SASPhile — Wed, 26 Aug 2009 20:52:14 GMT

Thanks Scott!
I created a dataset using the describe statement and arranged the variables of the order i want and appened the original dataset to it.

Re: rearrae variables

sbb — Wed, 26 Aug 2009 21:11:42 GMT

Humbly, you still have not explained why it is important to know or even care how SAS stores the information internally, even if you had the capability or control. However, as mentioned, the RETAIN statement, though I don't recommend it, will give you to establish a "default order" (not to be confused with any SAS-managed internal sequence) for the variables/columns in your SAS dataset/table.

Scott Barry
SBBWorks, Inc.

Re: rearrae variables

data_null__ — Thu, 27 Aug 2009 15:50:50 GMT

> Humbly, you still have not explained why it is
> important to know or even care how SAS stores the
> information internally, even if you had the
> capability or control. However, as mentioned, the
> RETAIN statement, though I don't recommend it, will
> give you to establish a "default order" (not to be
> confused with any SAS-managed internal sequence) for
> the variables/columns in your SAS dataset/table.

I think the OP means VARNUM order. We know that SAS doesn't necessary store the columns in VARNUM order but that is unimportant for this discussion.

VARNUM order is not important most of the time but there are times when clients want variables in a certain order. When that is the case I usually create the variable in the proper order. However there are times when it is necessary to move the columns around, and for that the RETAIN statement is the most concise method. I know that some SAS users have "retain-a-phobia". Perhaps the following example will help.

[pre]
proc contents data=sashelp.shoes order=varnum;
run;
data newShoes;
stop;
retain
Inventory
Region
Product
Subsidiary
Stores
Sales
Returns
;
set sashelp.shoes;
run;
data newShoes;
set newShoes sashelp.shoes;
*** Perhaps more code;
run;
proc contents data=newShoes order=varnum;
run;
[/pre]

Re: rearrae variables

sbb — Thu, 27 Aug 2009 16:28:50 GMT

Wait - that really didn't feel so bad!

Thanks data _null_;

Scott