topic Re: Splitting an Employee_Name (Last Name, First Name) to (First Name Last Name) in SAS Programming

Splitting an Employee_Name (Last Name, First Name) to (First Name Last Name)

loubams3 — Fri, 25 Jan 2019 18:43:32 GMT

proc sql;
select Manager_Name, Employee_Name, Total_Sales format=COMMA10.2
from (select l.Employee_ID, sum(Total_Retail_Price) as Total_Sales, Employee_Name, Country
from orion.order_fact as l,
orion.employee_addresses as p
where l.Employee_ID=p.Employee_ID and year(Order_Date)=2011
Group by Country, l.Employee_ID, Employee_Name) as l,
(select l.Employee_ID, Manager_ID, Employee_Name as Manager_Name
from orion.employee_organization as l,
orion.employee_addresses as p
where l.Manager_ID=p.Employee_ID) as p
where l.Employee_ID=p.Employee_ID
Order by Country, Manager_Name, Total_Sales desc;
quit;

Employee_Name is shown as Zhou, Tom, but I need it to change it to Tom Zhou

Re: Splitting an Employee_Name (Last Name, First Name) to (First Name Last Name)

PeterClemmensen — Fri, 25 Jan 2019 18:49:48 GMT

Classic use of the PRXCHANGE Function

I stole this example directly from the PRXCHANGE Documentation (Example 2).

data ReversedNames;
   input name & $32.;
   datalines;
Jones, Fred
Kavich, Kate
Turley, Ron
Dulix, Yolanda
Zhou, Tom
;
proc sql;
   create table names as
   select prxchange('s/(\w+), (\w+)/$2 $1/', -1, name) as name
   from ReversedNames;
quit;
proc print data=names;
run;

Re: Splitting an Employee_Name (Last Name, First Name) to (First Name Last Name)

Reeza — Fri, 25 Jan 2019 18:50:26 GMT

If the data is well formatted you can easily use SCAN() as well.

catx(" ", scan(employee_name, 2, ","), scan(employee_name, 1, ",")) as new_name

Re: Splitting an Employee_Name (Last Name, First Name) to (First Name Last Name)

novinosrin — Fri, 25 Jan 2019 19:05:28 GMT

@Reeza is brilliant to answer at lightning speed making me jealous while i was testing the same with 50 million records on my machine

here is the log difference. Kudos Reeza

361  proc sql;
362     create table names as
363     select prxchange('s/(\w+), (\w+)/$2 $1/', -1, name) as name
364     from ReversedNames;
NOTE: Table WORK.NAMES created, with 50000000 rows and 1 columns.

365  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           1:39.26
      cpu time            33.20 seconds


366  data names;
367   set ReversedNames;
368   name=catx(', ',scan(name,-1),scan(name,1));
369   run;

NOTE: There were 50000000 observations read from the data set WORK.REVERSEDNAMES.
NOTE: The data set WORK.WANT has 50000000 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           10.46 seconds
      cpu time            8.90 seconds

@mkeintz mentioned PRX being very slow in another thread and the above is the proof. But Mark if you have a minute, do you know why so? Is there an implementation problem? I am just curious

Re: Splitting an Employee_Name (Last Name, First Name) to (First Name Last Name)

PaigeMiller — Fri, 25 Jan 2019 19:09:15 GMT

Is it PRX that is slow, or is it doing this operation in SQL is the cause of the slowness? What happens if you try the PRX in a data step?

Re: Splitting an Employee_Name (Last Name, First Name) to (First Name Last Name)

novinosrin — Fri, 25 Jan 2019 19:11:25 GMT

@PaigeMiller Like I always said, you are full of brains, much better

370  data names;
371   set ReversedNames;
372   name=prxchange('s/(\w+), (\w+)/$2 $1/', -1, name);
373   run;

NOTE: There were 50000000 observations read from the data set WORK.REVERSEDNAMES.
NOTE: The data set WORK.NAMES has 50000000 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           32.99 seconds
      cpu time            22.29 seconds

Re: Splitting an Employee_Name (Last Name, First Name) to (First Name Last Name)

novinosrin — Fri, 25 Jan 2019 19:12:01 GMT

Great presence of mind!!!!!!!!

Re: Splitting an Employee_Name (Last Name, First Name) to (First Name Last Name)

Reeza — Fri, 25 Jan 2019 19:13:14 GMT

https://support.sas.com/resources/papers/proceedings/proceedings/sugi30/162-30.pdf

https://support.sas.com/resources/papers/proceedings/proceedings/sugi30/178-30.pdf

Re: Splitting an Employee_Name (Last Name, First Name) to (First Name Last Name)

PaigeMiller — Fri, 25 Jan 2019 19:19:25 GMT

So PRX is still approximately 3X slower than doing it with the SCAN function.

Re: Splitting an Employee_Name (Last Name, First Name) to (First Name Last Name)

novinosrin — Fri, 25 Jan 2019 19:20:14 GMT

Thank you. Looks interesting. Will take a serious look.

For those who work on large datasets on prod environments, dealing with humongous data

1. SQL processor doesn't seem to compete well with a datastep

2. PRX if not avoidable coz some manipulations are done best with PRX, otherwise stick to SAS functions

Disclaimer: Nonetheless,only extensive testing with rich samples can only confirm

Re: Splitting an Employee_Name (Last Name, First Name) to (First Name Last Name)

Reeza — Fri, 25 Jan 2019 19:20:26 GMT

@novinosrin I just use SCAN() because I find it's easier to remember the parameters, has nothing to do with efficiency in terms of computer, but efficiency in terms of typing and my time. I should really just sit down one day and learn them 😄

Re: Splitting an Employee_Name (Last Name, First Name) to (First Name Last Name)

novinosrin — Fri, 25 Jan 2019 19:25:49 GMT

" it's easier to remember the parameters," sounds like invariably efficient mind. Like @PaigeMiller pointed out "So PRX is still approximately 3X slower than doing it with the SCAN function." plus SQL processor makes it all the more time consuming. Jeez! what if we were to deal with 5 years transaction data as population for customer spends analysis for campaign targeting models. I can only imagine the size of the data would let us have lunch session and coffee session combines and process might still be running 🙂

I learned something from OP, You, draycut and ofcourse Mr. Quantmiller

@Reeza wrote:

@novinosrin I just use SCAN() because I find it's easier to remember the parameters, has nothing to do with efficiency in terms of computer, but efficiency in terms of typing and my time. I should really just sit down one day and learn them 😄

Re: Splitting an Employee_Name (Last Name, First Name) to (First Name Last Name)

PaigeMiller — Fri, 25 Jan 2019 19:43:09 GMT

I can only imagine the size of the data would let us have lunch session and coffee session ...

The first company to ever allow its employees to have coffee breaks is located about one mile from here in Buffalo, NY. It is the Barcalo Manufacturing Company, now known as Barcalounger (because they also invented the first recliner, the Barcalounger).