topic Re: how to assign sequence number in SAS Programming

how to assign sequence number

thanikondharish — Fri, 05 Mar 2021 07:34:17 GMT

id~class~section~mark~stagen~seq~test
101~class1~sectiona~~4~1~
101~class1~sectiona~fi~3~2~fi
101~class1~sectiona~~9~3~fi
101~class1~sectiona~~9~4~fi
101~class1~sectiona~~9~5~fi
101~class1~sectiona~last~6~6~last
101~class1~sectiona~~2~7~last
101~class1~sectiona~~3~8~last
101~class1~sectiona~fi~9~9~fi
101~class1~sectiona~~2~10~fi
101~class1~sectiona~~3~11~fi
101~class1~sectiona~~9~12~fi
101~class1~sectiona~last~2~13~last
101~class1~sectiona~~9~14~last
Note: delimiter is '~'

Hi I have one dataset like above and I have to create sequence number test wise

By variables are id class section seq.

How to create one variable (repeating) output should be below

for your ref:

id~class~section~mark~stagen~seq~test_repeating

101~class1~sectiona~~4~1~
101~class1~sectiona~fi~3~2~fi~1
101~class1~sectiona~~9~3~fi~2
101~class1~sectiona~~9~4~fi~3
101~class1~sectiona~~9~5~fi~4
101~class1~sectiona~last~6~6~last~1
101~class1~sectiona~~2~7~last~
101~class1~sectiona~~3~8~last~
101~class1~sectiona~fi~9~9~fi~1
101~class1~sectiona~~2~10~fi~2
101~class1~sectiona~~3~11~fi~3
101~class1~sectiona~~9~12~fi~4
101~class1~sectiona~last~2~13~last~1
101~class1~sectiona~~9~14~last~

only test='fi' values should go with sequence and test='last' only one number like above dataset

Re: how to assign sequence number

ChrisNZ — Fri, 05 Mar 2021 08:24:07 GMT

Is this in a text file?

Re: how to assign sequence number

thanikondharish — Fri, 05 Mar 2021 08:25:45 GMT

You can assume both TXT file or normally entered data

Re: how to assign sequence number

Shmuel — Fri, 05 Mar 2021 08:43:45 GMT

Next code is rested:

data have;
   infile datalines dlm='~' dsd truncover;
   input id class $ section $ mark $ stagen seq test $;
datalines;
101~class1~sectiona~~4~1~
101~class1~sectiona~fi~3~2~fi
101~class1~sectiona~~9~3~fi
101~class1~sectiona~~9~4~fi
101~class1~sectiona~~9~5~fi
101~class1~sectiona~last~6~6~last
101~class1~sectiona~~2~7~last
101~class1~sectiona~~3~8~last
101~class1~sectiona~fi~9~9~fi
101~class1~sectiona~~2~10~fi
101~class1~sectiona~~3~11~fi
101~class1~sectiona~~9~12~fi
101~class1~sectiona~last~2~13~last
101~class1~sectiona~~9~14~last
; run;

data want;
 set have;
  by ID class;
     retain test_repeating flag;
     if first.class then do;
	    test_repeating=.;
		flag=.;
	 end;
	 if test='fi' then do;
	    if flag=. then do; test_repeating = 1;  flag=1; end;
	    else test_repeating+1; 
	 end; else
	 if test='last' then do;
	    test_repeating=.; 
	    if flag = 1 then do; 
		    test_repeating=1; 
		    flag=.; 
		end;
	 end;
	 drop flag;
run;

Re: how to assign sequence number

ChrisNZ — Mon, 08 Mar 2021 20:44:55 GMT

This modifies the text file in place.

data _null_;  %* create text file;
  file "&wdir\test.txt";
  put '101~class1~sectiona~~4~1~';
  put '101~class1~sectiona~fi~3~2~fi';
  put '101~class1~sectiona~~9~3~fi';
  put '101~class1~sectiona~~9~4~fi';
  put '101~class1~sectiona~~9~5~fi';
  put '101~class1~sectiona~last~6~6~last';
  put '101~class1~sectiona~~2~7~last';
  put '101~class1~sectiona~~3~8~last';
  put '101~class1~sectiona~fi~9~9~fi';
  put '101~class1~sectiona~~2~10~fi';
  put '101~class1~sectiona~~3~11~fi';
  put '101~class1~sectiona~~9~12~fi';
  put '101~class1~sectiona~last~2~13~last';
  put '101~class1~sectiona~~9~14~last';
run;

data _null_;   %* alter text file;
  infile  "&wdir\test.txt" sharebuffers;
  input ;
  file  "&wdir\test.txt"; 
  if index(_INFILE_,'~fi') then do;
    N+1;
    LINE= cats(_INFILE_,'~',N); 
    put LINE; 
  end;
  if N & index(_INFILE_,'~last') then do;
    N=0;
    LINE= catt(_INFILE_,'~1');  
    put LINE;
  end;
run;

data _null_;  %* print text file;
  infile "&wdir\test.txt";
  input;
  put _infile_;
run;