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?
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.
Thank you Doc. To use the Rank task, I need to set data type of PhoneNumbers to numeric. Am I correct?
I don't recall. check the icon next to the role.
The icon was numeric only. I'll change the type to numeric and run. Thank you.
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
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
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
Actually, my solution does not address your problem. Apologies.
Nick
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.
Thank you Risa. It worked perfectly. After assigning the numbers, I could use the Split function. Thank you so much!
Yes, that worked and it is ingenious in its simplicity. Thank you.
Hi ,
This one can work out mostly,
data x;
set datasetname;
by name notsorted;
if first.name then sequence = 1;
else sequence + 1;
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!
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.