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?
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
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
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
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.
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;
No, you won't have to. It will be automatically done. Try it, and let us know how it goes.
Haikuo
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.
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
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;
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
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?
No, they are independent process, you choose to run ONLY one of them.
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.
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.