topic Re: extract domain from email in proc sq; in SAS Programming

extract domain from email in proc sql;

BrahmanandaRao — Sun, 20 Feb 2022 19:01:10 GMT

DATA WORK.Have;
FORMAT 		emailaddr $50.; 
INFORMAT	emailaddr $50.; 
INPUT  		emailaddr; 
CARDS;      
AdamApple@gmail.com
BillBobington@yahoo.com
ChuckCooper@hotmail.com
;
run;

data edomain;
set have;
dom=substr(emailaddr,findc(emailaddr,'@'));
run;

hi how to extract domain name from email using proc sql

Re: extract domain from email in proc sq;

PaigeMiller — Thu, 31 Dec 2020 12:30:12 GMT

length dom $ 64;
dom=scan(emailaddr,2,'@');

Re: extract domain from email in proc sq;

PGStats — Thu, 31 Dec 2020 17:30:24 GMT

proc sql;

create table edomain as

select *, scan(emailaddr, 2, '@') as dom length=50

from have;

quit;

Re: extract domain from email in proc sq;

BrahmanandaRao — Sun, 20 Feb 2022 09:41:32 GMT

How to get same domain emails count in sas

Re: extract domain from email in proc sq;

PaigeMiller — Sun, 20 Feb 2022 10:07:10 GMT

@BrahmanandaRao wrote:

How to get same domain emails count in sas

Please describe the problem in more detail. Please use complete sentences.

Re: extract domain from email in proc sq;

BrahmanandaRao — Sun, 20 Feb 2022 10:08:55 GMT

data emails;
input emails$ 50.;
datalines;
xxxxxxxxx@gmail.com
xxxxxxx@gmail.com
xxxxxxxx@outlook.com
xxxxxx@outlook.com
xxxx@gmail.com
;
run;

proc sql;
create table email_domain as
select *,scan(emails,-2 ,'@.') as domain
from emails;
quit;

proc sql;
select domain, count(domain) from email_domain
group by domain;
quit;

i want to count same domain names in email address any easy approch other than my code

Re: extract domain from email in proc sq;

Kurt_Bremser — Sun, 20 Feb 2022 11:12:51 GMT

You can combine both SQL queries into one:

proc sql;
create table email_domain as
select scan(emails,-2 ,'@.') as domain, count(*) as count
from emails
group by domain;
quit;

If the newly created variable had the same name as one contained in the input datasets, use the keyword CALCULATED in the GROUP BY clause.