BookmarkSubscribeRSS Feed
Jonate_H
Quartz | Level 8

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;

 

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

data_null__
Jade | Level 19

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

Reeza
Super User
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.
Jonate_H
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 951 views
  • 3 likes
  • 4 in conversation