Help using Base SAS procedures

rearranging variables back to the original datasets

Accepted Solution Solved
Reply
Contributor mei
Contributor
Posts: 62
Accepted Solution

rearranging variables back to the original datasets

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?


Accepted Solutions
Solution
‎03-28-2012 04:47 AM
Super User
Posts: 9,656

rearranging variables back to the original datasets

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

View solution in original post


All Replies
Solution
‎03-28-2012 04:47 AM
Super User
Posts: 9,656

rearranging variables back to the original datasets

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

Contributor mei
Contributor
Posts: 62

rearranging variables back to the original datasets

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

Super User
Super User
Posts: 6,494

rearranging variables back to the original datasets

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.

Contributor mei
Contributor
Posts: 62

rearranging variables back to the original datasets

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;

Respected Advisor
Posts: 3,124

Re: rearranging variables back to the original datasets

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

Haikuo

Super User
Super User
Posts: 6,494

rearranging variables back to the original datasets

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.

Contributor mei
Contributor
Posts: 62

rearranging variables back to the original datasets

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

Super User
Super User
Posts: 6,494

rearranging variables back to the original datasets

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;

Respected Advisor
Posts: 3,124

Re: rearranging variables back to the original datasets

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

Contributor mei
Contributor
Posts: 62

rearranging variables back to the original datasets

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?

Respected Advisor
Posts: 3,124

rearranging variables back to the original datasets

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 856 views
  • 2 likes
  • 4 in conversation