Hi everyone,
I have a huge dataset and i have different variables with prefix _1 to _108, and I want to reorder them by the prefix. For example, the order should be a_1, b_1 ... z_1 then a_2, b_2 ..., z_2. I used code
Select name into :var_1 separated by ","
from x
where name like '%_1';
And
Proc sql;
create table xxx as
select ID
,&var_1.
from xx
I just those codes 108 times because I do not know how to use loop to do it.
And I got a result only the prefix with _10, _20, _30 ...., _90 has the correct order that I want. The log shows that the rest variables already existsz on file work.xxx.
Please Help, THANKS!
Try
select trim(NAME) into :vars separated by ','
from HAVE
where NAME ne 'ID'
sorted by scan(NAME,2,'_'), NAME;
then
create table WANT as
select ID, &vars from HAVE;
order by not sorted by. Sorry.
Sort by numeric value then
order by input(scan(NAME,2,'_'),best.) , NAME;
"Invalid argument to function INPUT. Missing values may be generated"
This means the second part of the name is not always a number. Use a case statement to check the result of the scan function.
Something like
order by case when compress(scan(NAME,2,'_'),,'d')=' ' then input(scan(NAME,2,'_'), best.) else 0 end, NAME;
Another way:
select trim(NAME) into :vars1 separated by ','
from HAVE
where compress(scan(NAME,2,'_'),,'d') ne ' '
or scan(NAME,2,'_') eq ' '
sorted NAME;
select trim(NAME) into :vars2 separated by ','
from HAVE
where compress(scan(NAME,2,'_'),,'d') eq ' '
and scan(NAME,2,'_') ne ' '
sorted by input(scan(NAME,2,'_'),best.), NAME;
create table WANT as
select &vars1, &vars2 from HAVE;
Transpose the dataset, sort by _NAME_, then transpose back (if that is useful at all, in about 99% of cases a wide dataset is useless for further processing).
Quick example:
data have;
input id $ _1 _5 _3;
datalines;
X 1 2 3
Y 6 5 4
;
proc transpose
data=have
out=long
;
by id;
var _:;
run;
data long2;
set long;
varnum = input(substr(_name_,2),best.);
run;
proc sort data=long2;
by id varnum;
run;
proc transpose
data=long2
out=want (drop=_name_)
;
by id;
var col1;
id _name_;
run;
Seriously consider what those _X variables contain, and if you're not better off by keeping the long dataset for further work, especially if you have lots of missing values.
Hi @ttqkroe,
With a suitable ORDER BY clause in a PROC SQL step you can create a huge variety of sort orders.
Example:
/* Create sample data for demonstration */
data have;
someID='001';
array _[*] othervar v_20 abc_5 k_108 abc_20 v_108 v_5 k_5 abc_108 k_20 (0:9);
run;
/* Create list of variable names, ordered like this:
1. names not containing an underscore, sorted by position in PDV
2. names containing an underscore, sorted by numeric suffix, then by prefix
*/
proc sql noprint;
select name into :varlist separated by ' '
from dictionary.columns
where libname='WORK' & memname='HAVE'
order by find(name,'_')>0, varnum*(~find(name,'_')), input(scan(name,2,'_'),32.), name;
quit;
%put &varlist; /* for a visual check in the log */
%put %length(&varlist); /* should be <65533 */
/* Create dataset with sorted variables */
data want;
retain &varlist;
set have;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.