Hi, I have a question about assigning same sequence numbers within each group of 2 variables. Thanks much for your help! My data looks like below. ID count 1 15 1 13 1 13 1 8 2 20 2 20 2 20 2 14 2 14 2 6 2 6     What I want is to create seq_ID as below. ID count seq_id 1 15 1 1 13 2 1 13 2 1 8 3 2 20 1 2 20 1 2 20 1 2 14 2 2 14 2 2 6 3 2 6 3   ... View more

Hi,   I have a question about defining macro. I included input file 1, input file 2, and year in a macro. When I ran the macro, it always showed a error that  Macro function %INDEX has too many arguments. If I cut this macro into several piece macros, there would no problem to run each of those macros. Here is my macro. Thanks so much in advance for your help!   %macro index(in1,in2,yy); data carr&yy; set &in1; if ...(some conditions here)... then interest = 1; else interest=0; if interest; run;   proc sql; create table finder&yy as select distinct id from carr&yy; quit; 　   proc sort data=&in2; by id; run; 　 data merged&yy; merge finder&yy(in=a) &in2(in=b); by id; if a and b;   if (year(DEATH_DT)~=&yy and MONS=12)or (year(DEATH_DT)=&yy and MONS=month(DEATH_DT) ) then ff=1; else ff=0;   *if ff=1;   run; %mend; %index(raw.myfile1,raw.myfile2,2012); ... View more

Hi,   I have a question about calculating mean of score within each school with additional condition. The variable for mean of score within each school [mean_score] has been created. I need create two more variables now and am not sure how to program. Below is a fake small dataset for program develop. Real data contains about 100,000 observations (students) belonging to about 100 schools.   First, I need create a variable for mean of score within each school but without the score of the same row. For example, for student_ID=123, it should be (95+89+95)/(4-1), which is without 98 (his own’s score) in numerator and minus 1 (observation) in denominator. For student_ID=124, it should be (98+89+95)/3, and so on.   Second, I need create a variable for mean of score within each school including additional number, which is 25 th percentile of score for ALL student (in the entire dataset, not within each school). For example, for student_ID=123, it should be (98+95+89+95+25 th percentile of score in the entire dataset)/(4+1). So this variable will be the same value for students within each school.   Many thanks in advance for your answer!!!   student_ID         school_ID            score     mean_score 123                         1                              98           94.25 124                         1                              95           94.25 125                         1                              89           94.25 126                         1                              95           94.25 127                         2                              96           80.2 128                         2                              78           80.2 129                         2                              85           80.2 130                         2                              74           80.2 131                         2                              68           80.2 132                         3                              98           87 133                         3                              84           87 134                         3                              85           87 135                         3                              75           87 136                         3                              99           87 137                         3                              81           87 ... View more