topic Re: How to increase a columns value by 1 based on it's max value? in New SAS User

How to increase a columns value by 1 based on it's max value?

cosmid — Fri, 11 Jun 2021 16:03:39 GMT

If I have a data like this:

data sample;
  input class x;
datalines;
1 1
1 2
2 1
2 2
2 3
3 5
3 6
run;

And I want the output to be:

I can get the max number from each class but I don't know how to proceed to the next step by increasing them by 1.

proc sql;
  select max(x) into :max_val
  from sample
  group by class;
quit;

Re: How to increase a columns value by 1 based on it's max value?

cosmid — Fri, 11 Jun 2021 16:05:56 GMT

I'm sorry. What I meant by increasing the value by 1 based on the max value, is to increase the value by 1 in the first row in each group and then for that group the value keeps getting increased by 1 from there on.

Re: How to increase a columns value by 1 based on it's max value?

Reeza — Fri, 11 Jun 2021 16:12:16 GMT

proc means data=sample noprint NWAY;
class class;
var x;
output out=maxes N=N Max = Max;
run;

data want;
set maxes;

do i=1 to n;
x = max + i ;
output;
end;

keep class x;
run;

@cosmid wrote:

If I have a data like this:
data sample;
  input class x;
datalines;
1 1
1 2
2 1
2 2
2 3
3 5
3 6
run;
And I want the output to be:
class x
  1     3
  1     4
  2     4
  2     5
  2     6
  3     7
  3     8
I can get the max number from each class but I don't know how to proceed to the next step by increasing them by 1.
proc sql;
  select max(x) into :max_val
  from sample
  group by class;
quit;

Re: How to increase a columns value by 1 based on it's max value?

cosmid — Fri, 11 Jun 2021 17:22:56 GMT

Wow! I didn't know you can use a proc means like that! Really learned a lot, thank you!

This works great for the sample data! Is there a way to keep other columns while increasing the value by 1? It seems the proc freq dropped other columns when I test it.

Re: How to increase a columns value by 1 based on it's max value?

Reeza — Fri, 11 Jun 2021 17:43:31 GMT

You can look at the ID statement in PROC MEANS to keep other variables or you can remerge it with your original data set to keep other columns.
There is no PROC FREQ in my code.

Re: How to increase a columns value by 1 based on it's max value?

Ksharp — Sat, 12 Jun 2021 12:08:15 GMT

data sample;
  input class x;
datalines;
1 1
1 2
2 1
2 2
2 3
3 5
3 6
;
data sample;
 set sample;
 by class;
 if first.class then n=0;
 n+1;
run;
proc sql;
create table want as
select *,sum(n,max(x)) as want
 from sample
  group by class
   order by 1,2;
quit;