topic rearranging variables back to the original datasets in SAS Procedures

rearranging variables back to the original datasets

mei — Wed, 28 Mar 2012 08:20:21 GMT

I have an output sas dataset that arrange variables by alphabetical orders. How do i arrange them back to the original order as in the raw data sets in the most efficient way?

rearranging variables back to the original datasets

Ksharp — Wed, 28 Mar 2012 08:47:57 GMT

options user=work;
data origin ;
 input d b c a;
cards;
1 2 3 4
;
run;
data output;
 input a b c d;
cards;
4 2 3 1
;
run;
proc sql ;
 select name into : list separated by ' '
  from dictionary.columns
   where libname='WORK' and memname='ORIGIN';
quit;
data want;
 retain &list;
 set output;
run;

Ksharp

rearranging variables back to the original datasets

Tom — Wed, 28 Mar 2012 11:24:15 GMT

If the set of variables are the same then you can do it by referencing the original dataset first so that SAS "sees" the variables in that order first.

Use OBS=0 dataset option to prevent SAS from actually reading any observations from that source.

data fixed ;

set original(obs=0) have ;

run;

Re: rearranging variables back to the original datasets

Haikuo — Wed, 28 Mar 2012 12:02:49 GMT

Nice, Tom! Thank you for sharing!

Following Tom's lead, there are some other variants such as:

data fixed;

merge origin (obs=0) output;

run;

data fixed;

if 0 then set origin;

set output;

run;

Or outrageously:

data fixed;

if _n_=1 then do;

dcl hash h(dataset: 'output', multidata:'y');

h.definekey(all:'y');

h.definedata(all:'y');

h.definedone();

dcl hiter hi('h');

end;

set origin (obs=1);

do _n_=hi.first() by 0 while (_n_=0);

output;

_n_=hi.next();

end;

run;

Haikuo

rearranging variables back to the original datasets

mei — Wed, 28 Mar 2012 12:22:31 GMT

Hi, Pls see question below in bold:

Ksharp wrote:
options user=work;
data origin ;
 input d b c a;
cards;
1 2 3 4
;
run;
data output;
 input a b c d;
cards;
4 2 3 1
;
 run;

Q: I have total 58 columns/variables, would the steps above very complicating by typing all the variables name and their sequence?


proc sql ;
 select name into : list separated by ' '
  from dictionary.columns
   where libname='WORK' and memname='ORIGIN';
quit;
data want;
 retain &list;
 set output;
run;






Ksharp

rearranging variables back to the original datasets

Tom — Wed, 28 Mar 2012 12:30:28 GMT

Not sure what the question is.

If the question is would 58 variables be a problem? then no. The macro variable LIST could hold hundreds or thousands of variable names.

If the quesiton is could you create the version with the variable names sorted by name then you can use the same method. Just add an ORDER BY clause to the SQL query. You could order by NAME to get alphabetical list. You could order by VARPOS to match the physical order in the dataset.

rearranging variables back to the original datasets

mei — Wed, 28 Mar 2012 12:36:38 GMT

hi Tom,

Forgive me if i ask stupid q, as i m not very familiar with sql..

my question is do i replace the ' d b c a ' with variables in my file? if yes, then i have to type all 58 variables?

options user=work;

data origin ;

input d b c a;

cards;

1 2 3 4

;

run;

data output;

input a b c d;

cards;

4 2 3 1

;

run;

rearranging variables back to the original datasets

mei — Wed, 28 Mar 2012 12:38:08 GMT

Tom wrote:

If the set of variables are the same then you can do it by referencing the original dataset first so that SAS "sees" the variables in that order first.

Use OBS=0 dataset option to prevent SAS from actually reading any observations from that source.

data fixed ;

set original(obs=0) have ;

run;

hi Tom, just clarify, is this run first before running kSharp program?

Re: rearranging variables back to the original datasets

Haikuo — Wed, 28 Mar 2012 12:38:55 GMT

No, you won't have to. It will be automatically done. Try it, and let us know how it goes.

Haikuo

rearranging variables back to the original datasets

Haikuo — Wed, 28 Mar 2012 12:40:36 GMT

No, they are independent process, you choose to run ONLY one of them.

rearranging variables back to the original datasets

Tom — Wed, 28 Mar 2012 15:41:41 GMT

Those two data steps when posted as an example, since we do not know the structure of your actual datasets. There is no need for you to type any variable names if you already have the two versions of the dataset.

rearranging variables back to the original datasets

mei — Fri, 30 Mar 2012 03:04:23 GMT

Thanks Ksharp，

my stupidity to run the first part of data step and corrupt the data...i don't need to do it as i already have the datasets.

running from the pro sql give me the answer!!

Thanks

mei