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

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
PaigeMiller
Diamond | Level 26
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

View solution in original post

5 REPLIES 5
PaigeMiller
Diamond | Level 26

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
Azeem112
Quartz | Level 8

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

 

thanks.

 

PaigeMiller
Diamond | Level 26

@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
Azeem112
Quartz | Level 8

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
Diamond | Level 26
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

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 729 views
  • 1 like
  • 2 in conversation