Desktop productivity for business analysts and programmers

Assign Sequential Numbers

Reply
Occasional Contributor ct
Occasional Contributor
Posts: 11

Assign Sequential Numbers

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
New Contributor
Posts: 3

Re: Assign Sequential Numbers

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 ;
Occasional Contributor ct
Occasional Contributor
Posts: 11

Assign Sequential Numbers

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?

Valued Guide
Posts: 2,111

Assign Sequential Numbers

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.

Occasional Contributor ct
Occasional Contributor
Posts: 11

Assign Sequential Numbers

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

Valued Guide
Posts: 2,111

Assign Sequential Numbers

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

Occasional Contributor ct
Occasional Contributor
Posts: 11

Assign Sequential Numbers

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

Frequent Contributor
Posts: 91

Assign Sequential Numbers

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

Occasional Contributor ct
Occasional Contributor
Posts: 11

Assign Sequential Numbers

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

Frequent Contributor
Posts: 91

Assign Sequential Numbers

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

Frequent Contributor
Posts: 91

Assign Sequential Numbers

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

Nick

Frequent Contributor
Posts: 83

Re: Assign Sequential Numbers

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.

Occasional Contributor ct
Occasional Contributor
Posts: 11

Re: Assign Sequential Numbers

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

Senior User
Posts: 1

Re: Assign Sequential Numbers

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

Ask a Question
Discussion stats
  • 13 replies
  • 5148 views
  • 1 like
  • 6 in conversation