Thanks @FredrikE It works! ... View more

I have modified your code a little bit and it works! @RW9 proc sql; create table WANT as select A.GROUP, A.TIME, B.VALUE from HAVE A left join (select distinct GROUP, VALUE from HAVE where VALUE ne .) B on A.GROUP=B.GROUP; quit;   ... View more

Thanks @RW9 for your advice. There is only one nonmissing value for each group. my data is monthly and therefore is indexed by month 1-1000, using Jan, Feb, Mar is a simplification.  I have tried the datastep method to create sample data but failed to do so. Sorry about it as I am very new to SAS.  By the way, I have tried your code and the following error message shows up: Would you please kindly advice on what to do with it?   Thanks ... View more

My SAS data is attached as follows. I could not upload the data directly. I am wondering whether macro is needed to store the month where the maximum value occured. Would you have any advice on how to store a value and call later?   The detailed question is as follows: if the var1_max = 100 for id i (i=1,2,3), I need to find and store the &month_max of month t  when the var2_max occurs in the benchmark group (group 4 in the sample data). After obtaining month_max t, I need to call this value later in the program and divide var1 by var1_max for id i in month month_max.    Thanks ... View more

Thanks for the advice from @Kurt_Bremser and @RW9   I have uploaded a sample data. I have figured out the maximum value of var1 and var2 withint each id group. The next question is that if the maxvar1 = 100 for group i (i=1,2,3), I need to find and store the month_max t  when the maxvar2 occurs in the benchmark group (group 4 in the sample data). After obtaining month_max t, I need to call this value later in the program and divide var1 by maxvar1 for group i in month month_max.    My code is as follows: it seems that my second part of the code has some problem.    proc means data=import_data noprint; var val1 val2; by id; output out = edit_data max=val1_max val2_max; run;    %MACRO month_max; data want; set edit_data ; by id month; if val2_max = 100 and id= 4; then month = &month_max; run;     Thanks! ... View more