BookmarkSubscribeRSS Feed
hexx18
Quartz | Level 8

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

 

 

11 REPLIES 11
Reeza
Super User

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

hexx18
Quartz | Level 8

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

 

 

 

 

Reeza
Super User

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? 

hexx18
Quartz | Level 8

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

Reeza
Super User

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. 

hexx18
Quartz | Level 8

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

 

Reeza
Super User

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);
hexx18
Quartz | Level 8

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

Reeza
Super User

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. 

jklaverstijn
Rhodochrosite | Level 12

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.

jklaverstijn
Rhodochrosite | Level 12

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.

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
  • 11 replies
  • 2386 views
  • 0 likes
  • 3 in conversation