How to assign sequencing and sub sequencing

[Azeem112]

I have a table that has a few columns, I need to work on three columns of character type. Function, title, and reference, 

Input

Function | Title       | reference

BA          | Title_1   |    Z 

IT           | Title_2   |    C

BA         | Title_3   |     A

MA        | Title_4   |     H

IT          | Title_5   |     F

I want to assign a 7.n sequence to each unique function and 7.n.m sequence to title based onn reference number

n and m are integers 1---n numbers

output would be

Function      | Title                | reference

7.1 BA         | 7.1.1 Title_3   |     A

7.1 BA         | 7.1.2 Title_1   |    Z 

7.2 IT          | 7.2.1 Title_2   |    C

7.2 IT          | 7.2.2 Title_5   |     F

7.3 MA        | 7.3.1 Title_4   |     H

using proc sort I've managed to sort and using by function I've managed to add sequence on unique function, then I'm using proc sql to update. but that's not a good approach, and I don't know how to do subsequence on the title.

below is my code

proc sort data = obs_start;
by function;
run;

data updatedFunction;
length updatedFunction $8000.;
count = 1;
   do until (last.function);
      set obs_start;
        by function notsorted;
		if first.function then
      		updatedFunction=cat('7.', count + 1 , " ", function );
		else updatedFunction=cat('7.', count , " ", function );
   	end;
	keep updatedFunction Function;
run;

proc sql noprint;
update obs_start t1
set function = (select updatedFunction from updatedFunction t2 where t1.Function eq t2.Function);
quit;

please guide that how the above functionality can be achieved using minimum steps. 

Thanks.

[PaigeMiller]

It's not clear how you go from input data to output data. It seems as if the output data is sorted by FUNCTION and REFERENCE. But your PROC SORT only sorts by FUNCTION.

Can you offer some clarification here?

[Azeem112]

In the code I'm only adding sequence to the function so I'm only sorting the function.

thanks.

[PaigeMiller]

---

@Azeem112 wrote:

In the code I'm only adding sequence to the function so I'm only sorting the function.

---

I don't think this answers my question. How do you wind up with an output (which you are showing us) that is sorted by both FUNCTION and REFERENCE? We need to understand how the ouptut was created or we can't provide code to get there.

And contrary to your statement above, you are adding sequence to two different columns.

Please clarify the entire process.

[Azeem112]

Output generation is in two process

part 1) sort the data by function and reference, the result would be

Function | Title                | reference

BA          | Title_3   |     A

BA          | Title_1   |    Z 

IT           | Title_2   |    C

IT           | Title_5   |     F

MA         | Title_4   |     H

Part 2) Add sequence in function as 7.n and subsequence in the title as 7.n.m, n and m are positve whole numbers starting from 1

the output of this process would be

Function      | Title                | reference

7.1 BA         | 7.1.1 Title_3   |     A

7.1 BA         | 7.1.2 Title_1   |    Z 

7.2 IT          | 7.2.1 Title_2   |    C

7.2 IT          | 7.2.2 Title_5   |     F

7.3 MA        | 7.3.1 Title_4   |     H

hope it explains the query

[PaigeMiller · Accepted solution]

proc sort data=have;
    by function reference;
run;

data want;
	length function title $ 16;
    set have;
    by function;
    if first.function then seq1+1;
    if first.function then seq2=0;
    seq2+1;
    function=cat('7.',seq1,' ',function);
    title=cat('7.',seq1,'.',seq2,' ',title);
    drop seq1 seq2;
run;