topic Re: Assign Sequential Numbers in SAS Enterprise Guide

Assign Sequential Numbers

ct — Mon, 02 May 2011 13:45:10 GMT

I would like to know how to assingn sequential numbers for a multi-value field in SAS EG. Here is an example:

Name, PhoneNumbers, PhoneSequentialNo
John Smith, 111-222-3333, Phone1
John Smith, 123-525-8888, Phone2
John Smith, 222-444-9999, Phone3
Mary Adams, 333-888-7777, Phone1
Mary Adams, 337-841-1111, Phone2

Re: Assign Sequential Numbers

NickT — Tue, 03 May 2011 04:35:34 GMT

If I understand your requirements correctly, this can be achieved in a code block as follows.

data have ;
length Name $50 PhoneNumbers $12 ;
input Name $ PhoneNumbers $ ;
infile cards dlm=',' missover ;
cards;
John Smith,111-222-3333
John Smith,123-525-8888
John Smith,222-444-9999
Mary Adams,333-888-7777
Mary Adams,337-841-1111
;
run;

proc sort data=have out=have_sorted ;
by Name ;
run;

data want ;
length PhoneSequentialNo $10 ;
set have ;
by Name ;
if first.Name then _count=1 ;
else _count+1 ;

PhoneSequentialNo=cats("Phone",_count) ;

drop _count ;
run ;

Assign Sequential Numbers

ct — Wed, 06 Jul 2011 14:47:03 GMT

Thank you NickT. Now, I would like to know how I can accomplish this task in Enterprise Guide without writing the code. Would somebody help me?

Assign Sequential Numbers

Doc_Duke — Wed, 06 Jul 2011 15:12:38 GMT

In EGuide, it requires multiple tasks. First you use the rank task to assign the sequential numbers with name (you may have to fiddle with the options so it won't use tied ranks). Then you do the concatenation in a Query Buider using the advanced expression builder.

Assign Sequential Numbers

ct — Wed, 06 Jul 2011 15:36:56 GMT

Thank you Doc. To use the Rank task, I need to set data type of PhoneNumbers to numeric. Am I correct?

Assign Sequential Numbers

Doc_Duke — Wed, 06 Jul 2011 15:55:46 GMT

I don't recall. check the icon next to the role.

Assign Sequential Numbers

ct — Wed, 06 Jul 2011 16:11:09 GMT

The icon was numeric only. I'll change the type to numeric and run. Thank you.

Assign Sequential Numbers

nrose — Sat, 09 Jul 2011 12:44:47 GMT

Hi,

You can also:

1. Using query builder, select the 'Name' variable and click 'select distinct values only' to create a new dataset.

2. User query builder and add a computed column 'count' defined with the function 'monotonic()'

3. Use query builder to merge back with the orginal dataset, and concatenate "Phone" and count.

This means you dont have to change the phone number to numeric.

Nick

Assign Sequential Numbers

ct — Mon, 11 Jul 2011 13:30:07 GMT

Thank you Nick. I created the computed column, Count1, with the function, monotonic(t1.Name). The Count1 column assigned sequential numbers, but they were for an entire data set as shown below. In other words, it didn't start from 1 for Mary Adams.

Name Phone Count1

John Smith 111-222-3333 1

John Smith 123-525-8888 2

John Smith 222-444-9999 3

Mary Adams 333-888-7777 4

Mary Adams 337-841-1111 5

Assign Sequential Numbers

nrose — Mon, 11 Jul 2011 21:16:12 GMT

The trick is to first create a datasets with distinct values for the 'Name' variable, then apply the monotonic function and merge back to the original dataset. This means having two nodes in EG, but I cant think of a way to do it in one step.

Hope thats clearer.

Nick

Assign Sequential Numbers

nrose — Mon, 11 Jul 2011 21:25:28 GMT

Actually, my solution does not address your problem. Apologies.

Nick

Re: Assign Sequential Numbers

Resa — Thu, 14 Jul 2011 14:48:53 GMT

What you can do is, starting from the outcome of the table that you have with created with monotonic, the following:

Determine the minimum value of count1 per name. In the results that you displayed that would be 1 for John Smith and 4 for Mary Adams.

Merge this info with the original outcome and compute a column that consists of the difference between count1, the minimum value and add 1.

This would result in:

Name Count1 Min_Count1 SeqNumber

John Smith 1 1 1

John Smith 2 1 2

John Smith 3 1 3

Mary Adams 4 4 1

Mary Adams 5 4 2

Hope this helps.

Re: Assign Sequential Numbers

ct — Thu, 14 Jul 2011 15:25:01 GMT

Thank you Risa. It worked perfectly. After assigning the numbers, I could use the Split function. Thank you so much!

Re: Assign Sequential Numbers

Andrew_Walk_WCC — Mon, 05 Dec 2016 14:31:45 GMT

Yes, that worked and it is ingenious in its simplicity. Thank you.

Re: Assign Sequential Numbers

s_manoj — Thu, 31 May 2018 08:23:07 GMT

Hi ,

This one can work out mostly,

data x;
set datasetname;
by name notsorted;
if first.name then sequence = 1;
else sequence + 1;
run;

Re: Assign Sequential Numbers

Patrick — Thu, 31 May 2018 10:27:32 GMT

@s_manoj

LOL! You're a tiny bit late with your answer.

On a more serious note: It's a "problem" of this forum that old questions don't get automatically closed and write protected after a certain amount of time.