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;
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.
The PROC SORT option sortseq=linguistic(numeric_collation=on) will order v1 v2 v3 v10 v11 in the "correct" order.
Thank you all for your kindly help! I have learnt a lot from the community.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.