BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mei
Calcite | Level 5 mei
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

11 REPLIES 11
Ksharp
Super User
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

mei
Calcite | Level 5 mei
Calcite | Level 5

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

Tom
Super User Tom
Super User

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.

mei
Calcite | Level 5 mei
Calcite | Level 5

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;

Haikuo
Onyx | Level 15

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

Haikuo

Tom
Super User Tom
Super User

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.

mei
Calcite | Level 5 mei
Calcite | Level 5

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

Tom
Super User Tom
Super User

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;

Haikuo
Onyx | Level 15

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

mei
Calcite | Level 5 mei
Calcite | Level 5

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?

Haikuo
Onyx | Level 15

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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