topic Re: Do loop in SAS Data Management

Do loop

Mathis1 — Fri, 29 May 2020 13:42:01 GMT

Hello,

I'm trying to generate a variable (named "NEW") that is set to i=1 when first.VAR is observed and then is incremented by one until last.VAR.

But i'd like that each time a new first.VAR is met, NEW is set to i+1.

And as a last requirement, i'd like that when NEW=12, then NEW+1 is set back to 1.

I tried something like this (In the attached table VAR is named PUIS_CLASS) :

data WAANT;
Set HAVE;
Retain NEW;
	Do i=1 to 12;
	By PUIS_CLASS;
	if first.PUIS_CLASS then NEW=i;
	else NEW=i+1;
	if last.PUIS_CLASS then output;
	end;
run;

But it's not the solution...

Thank you again for your help 🙂

Re: Do loop

Tom — Fri, 29 May 2020 13:51:52 GMT

Either just test the value of NEW to see if it has gotten too large.

data want ;
  set have;
  by puis_class;
  new+1;
  if first.puis_class or (new > 12) then new=1;
run;

Or put the SET statement inside the DO loop.

data want ;
  do new=1 to 12 until (last.puis_class);
    set have;
    by puis_class;
    output;
  end;
run;

Re: Do loop

Mathis1 — Fri, 29 May 2020 14:02:54 GMT

Thank you for your response.

the problem is, I would like that when the first PUIS_CLASS="10_K" is met, New=2 and not 1. And when the first PUIS_CLASS="10_N" is observed, New=3 etc...

Re: Do loop

ballardw — Fri, 29 May 2020 14:05:32 GMT

Example of the desired output for the given input.

Re: Do loop

ed_sas_member — Fri, 29 May 2020 14:25:29 GMT

Hi @Mathis1

Would this code give the desired output?

data want;
	set have;
	by PUIS_CLASS;
	retain FLAG -1;
	if first.PUIS_CLASS then FLAG + 1;
	if first.PUIS_CLASS then NEW = FLAG;
	NEW + 1;
	if NEW = 12 then NEW = 1;
run;

Best,

Re: Do loop

Mathis1 — Fri, 29 May 2020 14:33:38 GMT

It works perfectly, thank you again 🙂

NB : for what i want exactly, it's :

if NEW = 13 then NEW = 1;

instead of NEW=12.

Re: Do loop

Mathis1 — Fri, 29 May 2020 15:23:13 GMT

Little rectification, in order for the code to work properly when FLAG exceeds 12, the code should be :

data want;
	set FORUM.HAVE;
	by PUIS_CLASS;
	retain FLAG -1;
	if first.PUIS_CLASS then FLAG + 1;
	If FLAG = 13 then FLAG = 0;
	if first.PUIS_CLASS then NEW = FLAG;
	NEW+1;
	if NEW = 13 then NEW = 1;
run;

🙂

Re: Do loop

Tom — Fri, 29 May 2020 16:15:17 GMT

So you want to create a sequence variable that starts at 1 for the first by group and 2 for the second by group?

Like this?

1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 ...
2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 ...
3 4 5 6 7 8 9 10 11 12 1 2 3 4 ...
4 5 6 7 8 9 10 11 12 1 2 3 4 ...
5 6 7 8 9 10 11 12 1 2 3 4 ...

If so then your current algorithm is breaking down after the 13th group.

From your sample data:

10_J --> 1 2 3 4 5 6 7 8 9 10 11 12
10_K --> 2 3 4 5 6 7 8 9 10 11 12 1
10_N --> 3 4 5 6 7 8 9 10 11 12 1 2
10_O --> 4 5 6 7 8 9 10 11 12 1 2 3
10_P --> 5 6 7 8 9 10 11 12 1 2 3 4
10_Q --> 6 7 8 9 10 11 12 1 2 3 4 5
10_R --> 7 8 9 10 11 12 1 2 3 4 5 6
10_S --> 8 9 10 11 12 1 2 3 4 5 6 7
11_M --> 9 10 11 12 1 2 3 4 5 6 7 8
11_N --> 10 11 12 1 2 3 4 5 6 7 8 9
11_O --> 11 12 1 2 3 4 5 6 7 8 9 10
11_P --> 12 1 2 3 4 5 6 7 8 9 10 11
11_Q --> 1 2 3 4 5 6 7 8 9 10 11 12
11_R --> 1 2 3 4 5 6 7 8 9 10 11 12

Notice how "11_R" is repeating the same sequence as "11_Q".

Try this code instead that will count the groups and the rows within the groups and then generate NEW using modulo (remainder) division.

data want2;
  do seq=0 by 1 until (last.puis_class);
    set have;
    by PUIS_CLASS;
    new = 1+mod(group+seq,12);
    output;
  end;
  group+1;
run;