BookmarkSubscribeRSS Feed
ct
Calcite | Level 5 ct
Calcite | Level 5
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
15 REPLIES 15
NickT
Fluorite | Level 6
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 ;
ct
Calcite | Level 5 ct
Calcite | Level 5

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?

Doc_Duke
Rhodochrosite | Level 12

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.

ct
Calcite | Level 5 ct
Calcite | Level 5

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

Doc_Duke
Rhodochrosite | Level 12

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

ct
Calcite | Level 5 ct
Calcite | Level 5

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

nrose
Quartz | Level 8

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

ct
Calcite | Level 5 ct
Calcite | Level 5

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

nrose
Quartz | Level 8

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

nrose
Quartz | Level 8

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

Nick

Resa
Pyrite | Level 9

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.

ct
Calcite | Level 5 ct
Calcite | Level 5

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

Andrew_Walk_WCC
Calcite | Level 5

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

s_manoj
Quartz | Level 8

Hi ,

This one can work out mostly,

 

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 15 replies
  • 17169 views
  • 1 like
  • 8 in conversation