DATA Step, Macro, Functions and more

Proc sort for character coloumns having numeric values

Reply
Contributor
Posts: 22

Proc sort for character coloumns having numeric values

Hi ,

I have two datasets B1 and B2 and i want to sort both datasets by first column can i do it dyanmically without indicating the name of teh column ?

 

B1

NUMBER ITEMS CODES
2 222223 2222
2 222228 2288
7 141111 545454
7 999999 5623
3 15151515 454545
1 8888888
484888

 
 

 

b2

 

ID ITEMS CODES
2 1151515 746467
2 1151313 746957
6 155444 48784
6 155255 48784
5 7884878 234454
1 7515787 4864487

 

 

%macro xc;

proc sort data=B_&i out= c_&i;

by ;

run;

%mend;

%xc;


i want to order by first column for B1 and B2 . So I have used this

%macro xc(i);

data _null_;
set sashelp.vcolumn;
where upcase(libname) = 'WORK' and upcase(memname) = upcase("B_&i");
if _n_ = 1
then do;
  call symput('varname',name);
  stop;
end;
run;

proc sort data=B_&i out=c_&i;
by &varname;
run;

%mend;

%xc(1);


what if My B2 Dataset looks like below


b2

 

ID ITEMS CODES
LIC  1151515 746467
LIC  1551547 48784
KKK 7884878 234454
KKK 7515787 4864487

 

 

Super User
Posts: 17,818

Re: Proc sort for character coloumns having numeric values

So what if your dataset looks like that? Does it not work?

Contributor
Posts: 22

Re: Proc sort for character coloumns having numeric values

[ Edited ]

Actually Number and  id coloumns are character and i changed them to numeric to use in proc sort but  when if i  encounter chacter data in ID coulumn and its a problem and now i am unable to sort

 

 

 

 

Super User
Posts: 17,818

Re: Proc sort for character coloumns having numeric values

How are you unable to sort? A character column sorts alphabetically. The code above doesn't require a numeric column. 

 

What at do you want that your not getting? 

Contributor
Posts: 22

Re: Proc sort for character coloumns having numeric values

was just checking instead can i add a column in B1 and B2  as count saying that  when number is 2 then 1 but when number changes to 7 then count+ 1 . same way for b2 . The count varibale should be dyanmic for B1 and B2 when i do some logic it should work for B1 and B2 irrespective whether the first column is numeric or character

 

B1

NUMBER ITEMS CODES
2 222223 2222
2 222228 2288
7 141111 545454
7 999999 5623
3 15151515 454545
1 8888888
484888

 

b2

 

ID ITEMS CODES
LIC  1151515 746467
LIC  1551547 48784
KKK 7884878 234454
KKK 7515787 4864487

 

 

 

Thanks

Super User
Posts: 17,818

Re: Proc sort for character coloumns having numeric values

Still not clear. What's your use case? The issue isn't a character column, but I don't know what your logic is. 

 

State what you need, not what you think the problem is. 

Contributor
Posts: 22

Re: Proc sort for character coloumns having numeric values

Hi ,

I have three data sets

 

B1

NUMBER ITEMS CODES
2 222223 2222
2 222228 2288
7 141111 545454
7 999999 5623
3 15151515 454545
1 8888888
484888


 

b2

 

ID ITEMS CODES
2 1151515 746467
2 1151313 746957
6 155444 48784
6 155255 48784
5 7884878 234454
1 7515787 4864487


b3

 

pin ITEMS CODES
LIC  1151515 746467
LIC  1551547 48784
KKK 7884878 234454
KKK 7515787 4864487

 

for all three tables except first column all others are same and first column is character

 

and i am taking first coulmn into macro variable  &varname  as shown above and i am doing sorting  but my b1 and b2 datsets doesnot get sorted by first coulum can anyone help

 

Super User
Posts: 17,818

Re: Proc sort for character coloumns having numeric values

I modified the macro very slightly to take a dataset name, rather than of the form name_i

 

This works for me, the numerical variables sort numerically and the character ones sort alphabetically. If this doesn't work, explain in DETAIL what is wrong, with a data example and your code/log if an error is generated.

 

%macro xc(datain);
data _null_;
set sashelp.vcolumn;
where upcase(libname) = 'WORK' and upcase(memname) = upcase("&datain");
if _n_ = 1
then do;
  call symputx('varname',name);
  stop;
end;
run;
proc sort data=&datain out=&datain._Sorted;
by &varname;
run;
%mend;

data b1;
input NUMBER ITEMS CODES;
cards;
2 222223 2222
2 222228 2288
7 141111 545454
7 999999 5623
3 15151515 454545
1 8888888 484888
;
run;

data b2;
input ID ITEMS CODES;
cards;
2 1151515 746467
2 1151313 746957
6 155444 48784
6 155255 48784
5 7884878 234454
1 7515787 4864487
;
run;

data b3;
input pin $ ITEMS CODES;
cards;
LIC  1151515 746467
LIC  1551547 48784
KKK 7884878 234454
KKK 7515787 4864487
;
run;

%xc(b1);
%xc(b2);
%xc(b3);
Contributor
Posts: 22

Re: Proc sort for character coloumns having numeric values

when i use below code

 

data xx;
input NUMBER$ ITEMS$;
datalines;
0 222223
1 222228
3 141111
4 999999
19 15151515
18 8888888 
run;

proc sort data=xx out=yy;
by number items;
run;

 

the output is

 

0 222223
1 222228
18 8888888
19 15151515
3 141111
4 999999

 

but i want them to be 0 1 3 4 ---

Super User
Posts: 17,818

Re: Proc sort for character coloumns having numeric values

That's not what your data has. Are you trying to replace the ID and create your own ID? That's a different question. 

 

This is why I've stated you should state your requirements. Please try stating them as clearly as possible from the beginning to the end. Not taking the time to formulate your question clearly is a majority of the issue. You need to understand your problem before you can solve, or we can help you. 

Super Contributor
Posts: 408

Re: Proc sort for character coloumns having numeric values

The sort order is correct. Your sort column look snumeric but is in fact character so it gets sorted alphabetically. The proper order in that case is 1 18 3 ...

 

What you want can be achieved by

 

data xx;
input NUMBER ITEMS $;
datalines;
0 222223
1 222228
3 141111
4 999999
19 15151515
18 8888888 
run;

So remove the dollar sign behind NUMBER. probably ITEMS deserves the same treatment. And add a space before the dollar sign for clarity.

 

- Jan.

Super Contributor
Posts: 408

Re: Proc sort for character coloumns having numeric values

Frankly speaking you no longer make sense. Do yo want to sort or add columns according to a specific rule (that may involve sorting)? Very different tasks indeed. Please rephrase and elaborate.

 

- Jan.

Ask a Question
Discussion stats
  • 11 replies
  • 364 views
  • 0 likes
  • 3 in conversation