Question about the order of memname and indsname

Reply
Frequent Contributor
Posts: 130

Question about the order of memname and indsname

[ Edited ]

In the following code1, the memname is sorted by ascending; but in the log window, the result of %put statement shows that the order of Y_names is like Y1 Y100 Y101...Y2, Y201...Y3...Y99

Does this mean the ascending order in code 1 is different from traditional ascending order? I thought that traditional ascending order should be like Y1 Y2 Y3....Y99 Y100 Y101...Y201;

 

In code 2, indsname order is also like the order of X1 X100 X101...X2, X201...X3...X99. How to make it in order X1 X2 X3....X99 X100 X101...X201 ?

 

 

*code 1;
proc sql print;
select distinct memname into :Y_names 
separated by ' ' 
from dictionary.tables 
where libname='WORK' and upcase(memname) like 'Y%'
order by memname asc;
%let y_numobs=&sqlobs; 
quit;
%put &Y_names;

/* The order is important because I will use 
%do i = 1 %to &y_numobs;
%let yy=%scan(&Y_names, &i, ' ');*/
*code 2; data alloutput; set &Y_names open=defer indsname=indsname; dataname = indsname; if lag(indsname) ne indsname then datacheck + 1; run;

 

Super User
Super User
Posts: 7,694

Re: Question about the order of memname and indsname

This is due to the fact that Y1, Y100 etc. are character values, not numeric, they are being sorted as text strings.  I wouldn't suggest doing what you are trying to do, macro variables and code is not great for processing long lists of variables and the code gets messy.  The simplest way of dealing with data of this type is to normalise it, i.e:

ID     YVAL     YRESULT

1       1           xyz

1       2           ...

 

The above structure will make you life and coding far easier.  Alternatively there is arrays:

data want;

  set have;

  array y{&y_numobs.};

  do i=1 to dim(y{*});

...

run;

 

This is just two ways of coding what you are trying to do using simple base SAS code.

Respected Advisor
Posts: 3,786

Re: Question about the order of memname and indsname

The PROC SORT option sortseq=linguistic(numeric_collation=on) will order v1 v2 v3 v10 v11 in the "correct" order.

Super User
Posts: 19,039

Re: Question about the order of memname and indsname

It sorts alphabetically, because the variable is text. Use the sort option specified by @Data_null_ to set the sort option so that it sorts numerically.
Frequent Contributor
Posts: 130

Re: Question about the order of memname and indsname

Thank you all for your kindly help! I have learnt a lot from the community. 

Ask a Question
Discussion stats
  • 4 replies
  • 408 views
  • 3 likes
  • 4 in conversation