sqlgroup-bysasdatastep

SAS - Increment a counting variable increasing whenver other variable in a group changes


I am basically trying to group by 2 variables and perform a proc sql mean, however my group by won't work for the following reason : here's an example of what I am trying to get (Assuming the data here is sorted properly)

What I'm trying to get

Now I know that this is not how the mean functions work so I've tried thinking about ways to resolve my issue and I've found that this would work :

What I am trying to get

However I don't know how I could obtain that. I have tried

data want; 
set have;
count + 1; 
by User Var X; 
if first.User then count = 1;
run;

However this does not seem to get the trick done. Any ideas on how to resolve this ?

Thanks for reading !


Solution

  • Like this?

    data have;
    input user $ varx value average;
    datalines;
    A 5  150 200 
    A 5  250 200 
    A 10 500 500 
    A 10 500 500 
    A 10 500 500 
    A 5  50  50  
    A 5  50  50  
    A 20 10  10  
    ;
    
    data want;
       set have;
       by user varx notsorted;
       if first.varx then count + 1;
    run;