topic Re: How to assign sequencing and sub sequencing in SAS Programming

How to assign sequencing and sub sequencing

Azeem112 — Sun, 04 Jul 2021 15:44:20 GMT

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.

Re: How to assign sequencing and sub sequencing

PaigeMiller — Sun, 04 Jul 2021 16:33:51 GMT

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?

Re: How to assign sequencing and sub sequencing

Azeem112 — Sun, 04 Jul 2021 17:35:05 GMT

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

thanks.

Re: How to assign sequencing and sub sequencing

PaigeMiller — Sun, 04 Jul 2021 17:46:21 GMT

@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.

Re: How to assign sequencing and sub sequencing

Azeem112 — Sun, 04 Jul 2021 18:09:41 GMT

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

Re: How to assign sequencing and sub sequencing

PaigeMiller — Sun, 04 Jul 2021 20:18:10 GMT

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;