topic Re: Looping code to sort into Quantiles in New SAS User

Looping code to sort into Quantiles

Gval99 — Wed, 19 Feb 2020 12:14:19 GMT

I previosly made a programme that sorts a large group of data into 10 equal parts , weighted by theyre values for feild A .

I have been trying to recreate it so that it works for any amount of equal parts .

here is my code for sorting the groups .

/*setting = 10 as an example*/

%LET n = 10;

data Weighted ;

set OGDATA;

i=1;

%LET GroupNo=&i;

Format Group $10.;

Do while (i<&n);

if Cumulative_A < (((i-1)/&n)*(Total_A))Then Group = "Group &GroupNo";

put i=;

keep Total_A Cumulative_A Group A ;

i+1;

end;

output;

Run;

proc summary data=Weighted ;

by Group ;

var Total_A ;

output out=SummaryBand sum= ;

run;

run ;

PROBLEMS :

code doesnt split the data correctly , it only gives G

Re: Looping code to sort into Quantiles

Patrick — Wed, 19 Feb 2020 13:11:59 GMT

Have you already tried if Proc Rank could do the job for you?

%LET n = 10;
data have;
  length a b 8;
  do b=1 to 100;
    a=ceil(ranuni(1)*100);
    output;
  end;
  stop;
run;
proc rank data=have out=want groups=&n;
  ranks group;
  var a;
run;
proc print data=want;
run;

Re: Looping code to sort into Quantiles

Gval99 — Wed, 19 Feb 2020 13:47:00 GMT

The problem with this solution is that the total values for A are not the same . The Results should be weighted so that for each group the Sum of A should be equal .
I did a proc summary of these results and it shows that is just breaks it down into an equal number of rows for A.

Re: Looping code to sort into Quantiles

mkeintz — Wed, 19 Feb 2020 15:22:05 GMT

What does you log say? When it sees the statement

%let groupno=&I;

doesn't SAs object saying "WARNING: Apparent symbolic reference I not resolved.".

Apparently (you haven't provided any sample input data) you have incoming data obs with variables TOTAL_A and CUMULATIVE_A. And you merely want to assign a group number to the obs based on the ratio of CUMULATIVE_A/TOTAL_A. categorizing into &N equal-size ranges.

Assuming that CUMULATIVE_A always more than zero, but never more than TOTAL_A then you have a very straightforward task:

%let N=10;
data weighted;
  set OGDATA;
  groupno =  ceil(cumulative_a/(total_a/&N));
run;