topic Re: max of numbers in SAS Programming

max of numbers

ziv — Mon, 13 Feb 2023 07:41:47 GMT

hi everyone

i have A like this:

i need b like this

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

thank you!!

Re: max of numbers

LinusH — Mon, 13 Feb 2023 08:40:47 GMT

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.

Re: max of numbers

s_lassen — Mon, 13 Feb 2023 09:14:13 GMT

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.

Re: max of numbers

Kurt_Bremser — Mon, 13 Feb 2023 11:12:14 GMT

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.

Re: max of numbers

ziv — Wed, 15 Feb 2023 14:51:07 GMT

work perfect !!!

thank_you!!

Re: max of numbers

ziv — Wed, 15 Feb 2023 15:49:45 GMT

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

Re: max of numbers

Kurt_Bremser — Wed, 15 Feb 2023 18:10:31 GMT

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.

Re: max of numbers

ziv — Thu, 16 Feb 2023 07:45:31 GMT

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

Re: max of numbers

Kurt_Bremser — Thu, 16 Feb 2023 11:30:31 GMT

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;

Re: max of numbers

ziv — Thu, 16 Feb 2023 13:34:31 GMT

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.

Re: max of numbers

Kurt_Bremser — Thu, 16 Feb 2023 15:35:58 GMT

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;

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

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

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