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
So what if your dataset looks like that? Does it not work?
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
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?
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
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.
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
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);
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 ---
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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.