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.
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;
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?
In the code I'm only adding sequence to the function so I'm only sorting the function.
thanks.
@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.
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.