max of numbers

[ziv]

hi everyone

i have A like this: 

a

1

2

3

4

1

2

3

1

2

3

4

5

 i need b like this 

b

4

4

4

4

3

3

3

5

5

5

5

5

that b will be the max of a when he start from 1 again

thank you!!

[LinusH]

Given that data is not huge:

- Save the row nuber as variable in the data set (rownum=_n_;)

- Sort the data set descending on rownum.

- RETAIN b, set it to a initially. 

- Create another retained flag variable to keep track on if A in the previous record was 1 or not. Assign b accordingly.

[s_lassen]

Assuming that you do not already have an ID for each group, I would suggest creating one, e.g.:

data grp;                                                                                                                               
  set have;                                                                                                                             
  if a=1 then grp+1;                                                                                                                    
run;    

When you have a grouping variable (here called GRP), the rest is easy:

data want;                                                                                                                              
  b=0;                                                                                                                                  
  do until(last.grp);                                                                                                                   
    set grp;                                                                                                                            
    by grp;                                                                                                                             
    b=max(b,a);                                                                                                                         
    end;                                                                                                                                
  do until(last.grp);                                                                                                                   
    set grp;                                                                                                                            
    by grp;                                                                                                                             
    output;                                                                                                                             
    end;                                                                                                                                
run;

If you already have a grouping variable, you can just use that. If your data is not sorted by the grouping variable (but the groups still come together), you can use the NOTSORTED option on the by statements, no need to sort first, unless you want to do that anyway.

[Kurt_Bremser · Accepted solution]

data want;
do until (next_a = 1 or done);
  merge
    have
    have (firstobs=2 keep=a rename=(a=next_a))
    end=done
  ; 
  b = max(b,a);
end;
do until (next_a = 1 or done2);
  merge
    have
    have (firstobs=2 keep=a rename=(a=next_a))
    end=done2
  ; 
  output;
end;
drop next_a;
run;

Untested, posted from my tablet.

This rather complicated method is designed to avoid a double physical read of the dataset, which should speed up the process for large datasets.

[ziv]

work perfect !!!

thank_you!!

[ziv]

Hi, the business is working.
But I have something I need to change and would appreciate your help.
I create variable A in the same table that I create variable B (its must be like this) , and if I do your actions in a separate table it works, and if I want it in the same table it causes disruptions, jumping numbers, etc.
Is there an option to write a command that will work in the same table?

Thank you very much in advance for the help

[Kurt_Bremser]

Post your existing dataset in usable form (as data step code with datalines), so we can see what you're talking about. Then also show the values you want to create.

[ziv]

Hey
The input is very long and not everything is relevant, I will send what is relevant.

I have 2 variables -
One called A runs from 1 until the variable B changes
B is a variable that changes every random number.
I need C to either give me the higher number of A or count B for me every time. (option for 2 different methods)
The problem is that I create and need to create A and B in the same command/table (necessary for other things) so I also need to create C together with them.

;data_work1
set work2
b= ...
if b = lag (a) then a+1;
else a=1;

here i need your code

run;

It's easiest to understand from the table that looks like this:

Thank you very much for your help!

A	B	C
1	XXX	9
2	XXX	9
3	XXX	9
4	XXX	9
5	XXX	9
6	XXX	9
7	XXX	9
8	XXX	9
9	XXX	9
1	BB	11
2	BB	11
3	BB	11
4	BB	11
5	BB	11
6	BB	11
7	BB	11
8	BB	11
9	BB	11
10	BB	11
11	BB	11
1	A	7
2	A	7
3	A	7
4	A	7
5	A	7
6	A	7
7	A	7
1	CC	6
2	CC	6
3	CC	6
4	CC	6
5	CC	6
6	CC	6

[Kurt_Bremser]

If you have A and B, then C is determined by

proc sql;
create table want as
  select
    a,
    b,
    max(a) as c
  from have
  group by b
  order by b, a
;
quit;

[ziv]

I'm sorry for contacting you so much, but my question is if the same thing is possible, just not in PROC SQL? The code I build before is with a loop written in the SAS language.

thank you.

[Kurt_Bremser]

Use a DOW loop:

data want;
do until (last.b);
  set have;
  by b;
  c = max(c,a);
end;
do until (last.b);
  set have;
  by b;
  output;
end;
run;