BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Quartz | Level 8

## How to assign sequencing and sub sequencing

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Diamond | Level 26

## Re: How to assign sequencing and sub sequencing

``````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;``````
--
Paige Miller
5 REPLIES 5
Diamond | Level 26

## Re: How to assign sequencing and sub sequencing

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?

--
Paige Miller
Quartz | Level 8

## Re: How to assign sequencing and sub sequencing

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

thanks.

Diamond | Level 26

## Re: How to assign sequencing and sub sequencing

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

--
Paige Miller
Quartz | Level 8

## Re: How to assign sequencing and sub sequencing

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

Diamond | Level 26

## Re: How to assign sequencing and sub sequencing

``````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;``````
--
Paige Miller
Discussion stats
• 5 replies
• 726 views
• 1 like
• 2 in conversation