topic Re: Proc sort for character coloumns having numeric values in SAS Programming

Proc sort for character coloumns having numeric values

hexx18 — Wed, 29 Jun 2016 20:38:50 GMT

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 ?

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

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

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

Re: Proc sort for character coloumns having numeric values

Reeza — Wed, 29 Jun 2016 21:02:29 GMT

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

Re: Proc sort for character coloumns having numeric values

hexx18 — Wed, 29 Jun 2016 21:07:47 GMT

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

Re: Proc sort for character coloumns having numeric values

hexx18 — Wed, 29 Jun 2016 21:16:55 GMT

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

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

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

Thanks

Re: Proc sort for character coloumns having numeric values

Reeza — Wed, 29 Jun 2016 21:17:47 GMT

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?

Re: Proc sort for character coloumns having numeric values

Reeza — Wed, 29 Jun 2016 21:19:45 GMT

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.

Re: Proc sort for character coloumns having numeric values

jklaverstijn — Wed, 29 Jun 2016 21:22:00 GMT

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.

Re: Proc sort for character coloumns having numeric values

hexx18 — Wed, 29 Jun 2016 21:35:23 GMT

Hi ,

I have three data sets

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

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

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

Re: Proc sort for character coloumns having numeric values

Reeza — Thu, 30 Jun 2016 00:04:08 GMT

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);

Re: Proc sort for character coloumns having numeric values

hexx18 — Thu, 30 Jun 2016 01:22:44 GMT

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 ---

Re: Proc sort for character coloumns having numeric values

Reeza — Thu, 30 Jun 2016 01:25:29 GMT

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.

Re: Proc sort for character coloumns having numeric values

jklaverstijn — Thu, 30 Jun 2016 06:29:17 GMT

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.