topic Re: Sort inside a varaible's value in SAS Enterprise Guide

Sort inside a varaible's value

luciacossaro — Wed, 30 May 2018 12:39:27 GMT

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 1	Another variable 2	Another variable 3	Vble_A	Vle_B	Another variable 4	Another variable 5	Another variable 6
			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

And I want to get this :

Another variable 1	Another variable 2	Another variable 3	Vble_A	Vle_B	Another variable 4	Another variable 5	Another variable 6
			BV	0,18
			BV	0,32
			BV	0,56
			BV	0,8
			BV	1,2
			XY	0,12
			XY	0,3
			XY	0,4
			XY	0,56
			XY	0,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!!

Re: Sort inside a varaible's value

Quentin — Wed, 30 May 2018 12:42:54 GMT

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.

Re: Sort inside a varaible's value

SuryaKiran — Wed, 30 May 2018 13:03:39 GMT

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;

Re: Sort inside a varaible's value

Astounding — Wed, 30 May 2018 13:18:03 GMT

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;

Re: Sort inside a varaible's value

Ksharp — Wed, 30 May 2018 13:48:33 GMT

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;

Re: Sort inside a varaible's value

ballardw — Wed, 30 May 2018 14:33:42 GMT

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.

Re: Sort inside a varaible's value

luciacossaro — Thu, 31 May 2018 13:31:03 GMT

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!

Re: Sort inside a varaible's value

luciacossaro — Thu, 31 May 2018 13:35:48 GMT

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

Re: Sort inside a varaible's value

Ksharp — Thu, 31 May 2018 13:57:58 GMT

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.

Re: Sort inside a varaible's value

luciacossaro — Thu, 31 May 2018 15:05:29 GMT

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;

Re: Sort inside a varaible's value

ballardw — Thu, 31 May 2018 17:17:04 GMT

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

Re: Sort inside a varaible's value

luciacossaro — Fri, 01 Jun 2018 12:32:00 GMT

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!

Re: Sort inside a varaible's value

Ksharp — Fri, 01 Jun 2018 12:56:11 GMT

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;

Re: Sort inside a varaible's value

luciacossaro — Tue, 05 Jun 2018 20:22:08 GMT

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