Desktop productivity for business analysts and programmers

Sort inside a varaible's value

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

Sort inside a varaible's value

[ Edited ]

Hi!

 

I need some help. I have a dataset with the follow structure(the are not the real name is only for exemple) and i want to order (descending) the Vble_B for each differnt value of Vble_A.

 

For exemple:

I have this:

Another variable 1Another variable 2Another variable 3Vble_AVle_BAnother variable 4Another variable 5Another variable 6
   XY0,6   
   XY0,3   
   XY0,4   
   XY0,12   
   XY0,56   
   BV0,18   
   BV0,56   
   BV1,2   
   BV0,32   
   BV0,8   

 

And I want to get this :

 

Another variable 1Another variable 2Another variable 3Vble_AVle_BAnother variable 4Another variable 5Another variable 6
   BV0,18   
   BV0,32   
   BV0,56   
   BV0,8   
   BV1,2   
   XY0,12   
   XY0,3   
   XY0,4   
   XY0,56   
   XY0,6   

 

I need do this to can choose then the higher 10%.

 

Do you have any idea to make that ? I tried with PROC sort and PROC sql/order by I can't get the right result.

 

Thank you very much!!


Accepted Solutions
Solution
2 weeks ago
Super User
Posts: 10,689

Re: Sort inside a varaible's value

Posted in reply to luciacossaro

OK. I understand what you mean now .

 

%let percent=0.1;

data class;
 set sashelp.class;
run;

proc summary data=class nway;
class sex;
var weight;
output out=n(drop=_:) n=freq;
run;
data n;
 set n;
 n=ceil(&percent*freq);
 drop freq;
run;
proc sort data=class;
by sex descending weight;
run;
data want;
 merge class n;
 by sex;
 if first.sex then count=0;
 count+1;
 if count le n;
 drop count n;
run;

View solution in original post


All Replies
PROC Star
Posts: 1,449

Re: Sort inside a varaible's value

Posted in reply to luciacossaro

Please show what you tried, and describe the problem with the result you got.

 

Did you try:

proc sort data=have;
  by Var_A Var_B;
run;

?

 

It looks to me like that should work, assuming you want an alphabetical sort order.  If not, there are other sorting options.

Contributor
Posts: 24

Re: Sort inside a varaible's value

I can do it; thank you. Now i have problems to choose the 10% higher  for each Vble_A's value. I tried that but it make 10 groups for all the varaibles and not for the value into each value of Vble_A. Do you have any idea ? (Sorry, i am a new sas user).

 

PROC rank DATA=SUP GROUPS=10 DESCENDING OUT=DIX_POURC_SUP;
BY CLE_UNIQUE ;
VAR RAPPORT
RANKS DECILE;
RUN;

 

Thank you very much!!

Valued Guide
Posts: 559

Re: Sort inside a varaible's value

[ Edited ]
Posted in reply to luciacossaro

You might try something like this

data have;
input 	Vble_A:$	Vle_B:$;
datalines;
XY 0,6
XY 0,3
XY 0,4
XY 0,12
XY 0,56
BV 0,18
BV 0,56
BV 1,2
BV 0,32
BV 0,8
;
run;

proc sql;
create table want as 
select *,INPUT(scan(Vle_B,1,','),3.) as one,INPUT(substr(scan(Vle_B,2,','),1,1),3.) as two from have
order by Vble_A,one,two
;
run;
Thanks,
Suryakiran
Super User
Posts: 6,632

Re: Sort inside a varaible's value

Posted in reply to luciacossaro

To do this easily, you need a little more control over your data.  In this case, when SAS sees "0,8" it has to decide whether that means "0,80" or whether it means "0,08".  SAS actually uses the wrong interpretation (wrong for your purposes) and interprets "0,8" as "0,08".  If you are able to change the data to insert the trailing "0" where appropriate, you could sort your data using:

 

proc sort  data=mydata sortseq=linguistic(Numeric_Collation=ON);

by vble_a vble_b;

run;

Super User
Posts: 10,689

Re: Sort inside a varaible's value

Posted in reply to luciacossaro
proc sort data=sashelp.heart out=heart;
 by sex;
run;

proc rank data=heart out=temp groups=10 descending;
by sex;
var weight;
ranks rank;
run;

data want;
 set temp;
 if rank=0;
run;
Contributor
Posts: 24

Re: Sort inside a varaible's value

Thank you for your advice. I tried that but it makes 10 decile for the variables in all of dataset (independiently of CLE_UNIQUE's value) and i need to get 10 decile for each value of CLE_UNIQUE.

 

I did that :

 

PROC rank DATA=SUP GROUPS=10 DESCENDING OUT=DIX_POURC_SUP;
BY CLE_UNIQUE ;
VAR RAPPORT;
RANKS DECILE;
RUN;

 

Do you know how this don't work ?

 

Thank you!

Super User
Posts: 10,689

Re: Sort inside a varaible's value

Posted in reply to luciacossaro

The code should work.

 

 

proc sort data=sashelp.class out=heart;
 by sex;
run;

proc rank data=heart out=temp groups=3 descending;
by sex;
var weight;
ranks rank;
run;

proc freq data=temp;
table sex*rank/list;
run;

 

 

Check OUTPUT.

 

Contributor
Posts: 24

Re: Sort inside a varaible's value

It don't work, I believe the proble is the number of group is biger than the number of observations in some Cle_unique's value.

If you try this code, it don't work. That is the problem with my dataset. I need to class observations to can choose 10% higher in each value of Cle_unique but sometimes this variable have 60 observation and sometime only 4.

 

Thank you for your advice.

 

proc sort data=sashelp.class out=heart;
 by sex;
run;

proc rank data=heart out=temp groups=15 descending;
by sex;
var weight;
ranks rank;
run;

proc freq data=temp;
table sex*rank/list;
run;
Super User
Posts: 13,321

Re: Sort inside a varaible's value

Posted in reply to luciacossaro

@luciacossaro wrote:

It don't work, I believe the proble is the number of group is biger than the number of observations in some Cle_unique's value.

If you try this code, it don't work. That is the problem with my dataset. I need to class observations to can choose 10% higher in each value of Cle_unique but sometimes this variable have 60 observation and sometime only 4.

 

Thank you for your advice.

 


You need to tell us exactly what does not work. You obviously expect a specific result. Please show what that explicit result would have been if that example code worked correctly.

Solution
2 weeks ago
Super User
Posts: 10,689

Re: Sort inside a varaible's value

Posted in reply to luciacossaro

OK. I understand what you mean now .

 

%let percent=0.1;

data class;
 set sashelp.class;
run;

proc summary data=class nway;
class sex;
var weight;
output out=n(drop=_:) n=freq;
run;
data n;
 set n;
 n=ceil(&percent*freq);
 drop freq;
run;
proc sort data=class;
by sex descending weight;
run;
data want;
 merge class n;
 by sex;
 if first.sex then count=0;
 count+1;
 if count le n;
 drop count n;
run;

Contributor
Posts: 24

Re: Sort inside a varaible's value

Thank you very much!! this is what i wanted; it's works.

 

 

 

Super User
Posts: 13,321

Re: Sort inside a varaible's value

Posted in reply to luciacossaro

proc sort data=have;

   by vble_a descending Vle_b;

run;

 

Should work if I understand your need and Vle_b is numeric. If Vle_b is not numeric but you want the contents to sort as if they are then you would add sortseq=linguistic (numeric_collation=on) to the proc statement.

 

if that does not work then you need a better description of your sort rule(s) and data examples.

Contributor
Posts: 24

Re: Sort inside a varaible's value

Hi; 

 

I have a dataset with a variable "ID" (char) and a variable "Price" (num) and some others variables.

I need split the dataset in 10 deciles (10% of observations in each) FOR EACH ID'S VALUE depending on price's values for after to can choose the highest 10% price's value for each ID'S value.

 

For the differents ID's values there is not the same number of observations. 

 

I tried with PROC RANK but i get deciles for all the dataset (not depending of each ID's value).

 

It should be relatively easy to do it but I am a new SAS user and I can not do it.

 

Do you have any idea ?

 

Thank you!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 215 views
  • 5 likes
  • 6 in conversation