topic Question about the order of memname and indsname in SAS Programming

Question about the order of memname and indsname

Jonate_H — Thu, 10 Dec 2015 09:17:41 GMT

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;

Re: Question about the order of memname and indsname

RW9 — Thu, 10 Dec 2015 09:35:53 GMT

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.

Re: Question about the order of memname and indsname

data_null__ — Thu, 10 Dec 2015 10:20:34 GMT

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

Re: Question about the order of memname and indsname

Reeza — Thu, 10 Dec 2015 15:51:20 GMT

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.

Re: Question about the order of memname and indsname

Jonate_H — Thu, 10 Dec 2015 16:26:35 GMT

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