BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mh2t
Obsidian | Level 7

I have a dataset :

 

varName   Levels   Freq     Value

  var1                        2          11

  var1             b         1          12.5

  var1             c         1          9.25

  var1             d         1          12.5

  var2                        1          9.25

  var2             n         3          8.75

  var2             o         1          8

  var2             p         1          9.35

  var2             q         3          8.15

  var2             r         1           5

 

and I want to bin the levels per variable based on their %0-25, %25-50, %50-75 and %75-100 percentiles of the values within the groups. So the data would like :

 

varName   Levels   Freq     Value    new_level

  var1                        2          11             2nd 

  var1             b         1          12.5          3rd

  var1             c         1          9.25          1st

  var1             d         1          12.5          3rd

  var2                        1          9.25          3rd

  var2             n         3          8.75           4th

  var2             o         1          8                2nd

  var2             p         1          9.35           4th

  var2             q         3          8.15           2nd

  var2             r         1           5               1st

 

Your help would be greatly appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
proc rank data=have groups=4 out=want;
by varName;
var value;
ranks new_level;
run;

View solution in original post

4 REPLIES 4
ballardw
Super User

Please tell us in terms of the variables that you show what defines a "group".

 

And I think you need to show what the actual 'percentile' value you are assigning to each of those values as I can't see any obvious fit. Are you expecting the freq to affect the percentile value?

 

 

mh2t
Obsidian | Level 7

@ballardw group is varName here. Value is representing the average per level. Freq does not affect the percentile value. Percentile relates to the rank order of the values.

ghosh
Barite | Level 11

Something like this?

proc univariate;
	by varname;
	weight freq;
	var value;
	output pctlpre=P_ 
              pctlpts=0 to 100 by 25;
run;

proc print;
run;
Ksharp
Super User
proc rank data=have groups=4 out=want;
by varName;
var value;
ranks new_level;
run;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 900 views
  • 1 like
  • 4 in conversation