topic Re: Change Variable order in SAS Programming

Change Variable order

Doug — Wed, 05 Dec 2012 19:22:59 GMT

I have a dataset in which I want the vars to appear in a predetermined order. The correct order is contained in another dataset. How can I transfer one to the other?

Re: Change Variable order

data_null__ — Wed, 05 Dec 2012 19:39:51 GMT

What exactly is the data that is "contained in another dataset"?

Re: Change Variable order

tish — Wed, 05 Dec 2012 19:43:23 GMT

You have to invoke PROC SQL twice, but you can capture the order in the other dataset by referring to values in the dictionary.columns dataset that is (quietly) available to you in PROC SQL. First you load the ordered columns into a macro variable (separated by commas). Then you go back and create a reordered dataset using the macro variable.

proc sql noprint;

select

name into: variable_order separated by ','

from

dictionary.columns

where

libname = 'WORK' and

memname = 'OTHER_DATASET';

quit;

%put variable_order= &variable_order;

proc sql;

create table THIS_DATASET_REORDERED as

select

&variable_order

from

THIS_DATASET;

quit;

Re: Change Variable order

tish — Wed, 05 Dec 2012 19:44:58 GMT

In dictionary.columns, the libname and memname variables are all upper case.

Re: Change Variable order

Doug — Wed, 05 Dec 2012 19:45:19 GMT

What happens is I have some character variables which are very long >200. If they meet this criterion, they are renamed and split into 200 character maximum fields. The original field is then removed. The issue arises when the new vars are placed last in the dataset since they were created last. So, in essence the datasets have the same data with the long fields broken into smaller parts. I want the split fields to appear where the original long variable appeared in the original dataset.

Re: Change Variable order

tish — Wed, 05 Dec 2012 19:54:31 GMT

Do you have a standard pattern for renaming the variables? Again, create the macro variable using the dictionary.columns file and then reprocess it in a macro, substituting the new names for the old ones. Go back into SQL to create a table with the new order.

Re: Change Variable order

Haikuo — Wed, 05 Dec 2012 20:49:26 GMT

When you say 200, is it the length of variable (content) or the length of variable name?

Re: Change Variable order

Doug — Wed, 05 Dec 2012 20:59:30 GMT

Length of the content 200+ characters

Re: Change Variable order

tish — Wed, 05 Dec 2012 22:29:10 GMT

I have *not* tested the following and there could be a syntax error or two. There could also be a faster way to do it. Please note that you can look at the dictionary.columns table: use the SAS explorer to open up the sashelp library. Scroll down to the Vcolumn icon and open that up: it's the same file.

***** grab information from the dictionary.columns table and put it into two macro variables now:

proc sql noprint;

select

name into: variable_order separated by ' '

from

dictionary.columns

where

libname = 'WORK' and

memname = 'OTHER_DATASET';

select

name into: long_ones separated by " "

from

dictionary.columns

where

libname = 'WORK' and

memname = 'OTHER_DATASET' and

type = "char" and

length >= 200;

quit;

%put variable_order= &variable_order;

%put long_ones= &long_ones;

***** Use a macro to read the two lists and create a third one, this time comma separated:

%MACRO create_new_list;

%global new_order;

%let start = 1;

%let new_order=;

%do

l = 1 %to %sysfunc(countw(&long_ones));

%let long_one = %scan(&long_ones, &l);

%do

i = &start %to %sysfunc(countw(&variable_order));

%let variable = %scan(&variable_order, &i);

%if

&variable ^= &long_one %then

%let new_order = &new_order.,&variable;

%else

%do;

%let new_order = &new_order.,&variable._1,&variable._2;

%let start = %eval(&i + 1);

%goto out:;

%end;

%out:

%end;

%MEND create_new_list;

%put new_order= &new_order;

***** Use the new variable to order the dataset in PROC SQL:

proc sql;

create table THIS_DATASET_REORDERED as

select

&new_order

from

THIS_DATASET;

quit;

I hope this helps!

Additional comment: this macro still needs to add any variables that appear after the last long variable. I could keep playing with it, but I can't tell if you're interested.

Re: Change Variable order

RD2 — Thu, 06 Dec 2012 09:23:42 GMT

How about this?

data have1;

input a b c;

cards;

1 2 3

;

data have2;

input b c a;

cards;

4 5 6

;

proc sql;

create table c as

select *

from have1

where 0

union

select *

from have2

;

quit;

Re: Change Variable order

TimArm — Thu, 06 Dec 2012 10:28:20 GMT

To try to answer your original question, have you tried this?...

data out;

set ordered (obs=0);

...

run;

If you include the ordered dataset as the first in the set statement, but with obs=0, it should define the variables you want in the order you want.

Re: Change Variable order

tacit — Thu, 06 Dec 2012 14:30:36 GMT

Just do this:

proc sql;

create table temp like OTHER_DATASET;

quit;

proc append base=temp data=THIS_DATASET;

run;

Re: Change Variable order

HWSteinberg — Mon, 10 Dec 2012 09:23:10 GMT

Just to complete this:

data out;

set ordered (obs=0) unordered;

...

run;

But: it works only correct, if variables in ordered have the same attributes as those in unordered.

Re: Change Variable order

Doug — Tue, 11 Dec 2012 14:39:51 GMT

Thanks! What I eneded up doing was using the chalk board to write the repeated dataset and retain statements. It took a while but it seems to work well.