Shows Variable XXX already exists on file work.XXX when I tried to reorder the variables by prefix

[ttqkroe]

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!

[ChrisNZ]

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;

[ttqkroe]

No, it does not work. There is a syntax error on "sorted by scan(Name,2,'_'), name"

[ChrisNZ]

order by not sorted by. Sorry.

[ttqkroe]

yeah. I tried order by, but there are new problem caused. The result looks like a_1 ... z_1, a_10 ... z_10, a_100 ... z_100, a_101 ... z_101

[ttqkroe]

I just realized that it cannot check the prefix as a whole thing. For 102, it will be order as 1 then 0 then 2 instead of 102 as a whole.

[ChrisNZ]

Sort by numeric value then

order by input(scan(NAME,2,'_'),best.) , NAME;

[ttqkroe]

it still does not work.
It says
" The query as specified involves ordering by an item that doesn't appear in its SELECT clause."
"Invalid string"
"Invalid argument to function INPUT. Missing values may be generated"

[ChrisNZ]

"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;

[ChrisNZ]

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;

[Kurt_Bremser]

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.

[FreelanceReinh]

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;