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 🙂
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,
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;
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...
Example of the desired output for the given input.
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,
It works perfectly, thank you again 🙂
NB : for what i want exactly, it's :
if NEW = 13 then NEW = 1;
instead of NEW=12.
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;
🙂
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;
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.