Separated by a comma

[eins]

Dear all,

 

A variable (which is "email address" of 10,000 persons) is in one column of the SPSS.  I want to produce output such that the email adds would be separated by comma.  For example, suppose the email adds of the five persons are:

 

smith@...

john@...

wyatt@...

watson@...

briggs@...

 

The desired output would be:

 

smith@..., john@..., wyatt@..., watson@..., briggs@...

 

Thank you.

Johnny

 

---

Yahoo! Toolbar is now powered with Free Anti-Virus and Anti-Adware Software. Download Yahoo! Toolbar now!

[Martins Liberts]

Add comma and space by CONCAT function and do FLIP procedure on dataset and write out variables to text file by WRITE.

eins wrote:

Dear all,
 
A variable (which is "email address" of 10,000 persons) is in one column of the SPSS.  I want to produce output such that the email adds would be separated by comma.  For example, suppose the email adds of the five persons are:
 
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
 
The desired output would be:
 
[e-mail], [e-mail], [e-mail], [e-mail], [e-mail]
 
Thank you.
Johnny
 

[Albert-jan Roskam]

... or simply like below. The code assumes that the email variable is the first variable in your file.

data list list (";") / email (a50).
begin data
smith@...
john@...
wyatt@...
watson@...
briggs@...
end data.

begin program.
import spss
curs = spss.Cursor([0])
f = open ("d:/temp/email_list.txt", "wb")
for record in range(spss.GetCaseCount()):
  case = curs.fetchone()[0]
  f.write(case.strip() + ", ")
curs.close()
f.close()
end program.

Cheers!!
Albert-Jan

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Before you criticize someone, walk a mile in their shoes, that way
when you do criticize them, you're a mile away and you have their shoes!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


--- On Thu, 10/15/09, Martins Liberts <martins.liberts@...> wrote:

> From: Martins Liberts <martins.liberts@...>
> Subject: Re: [SPSSX-L] Separated by a comma
> To: SPSSX-L@...
> Date: Thursday, October 15, 2009, 8:11 AM
> Add comma and space by CONCAT
> function and do FLIP procedure on dataset and
> write out variables to text file by WRITE.
>
>
>
>
> eins wrote:
> >
> > Dear all,
> > Â
> > A variable (which is "email address" of 10,000
> persons)Â is in one column
> > of the SPSS.  I want to produce output such
> that the email adds would be
> > separated by comma.  For example, suppose the
> email adds of the five
> > persons are:
> > Â
> > smith@...
> > john@...
> > wyatt@...
> > watson@...
> > briggs@...
> > Â
> > The desired output would be:
> > Â
> > smith@...,
> john@..., wyatt@...,
> watson@...,
> > briggs@...
> > Â
> > Thank you.
> > Johnny
> > Â
> >
> >
> >
> >
>
>
> -----
> --
> Martins Liberts
> --
> View this message in context: http://www.nabble.com/Separated-by-a-comma-tp25902488p25903488.html
> Sent from the SPSSX Discussion mailing list archive at
> Nabble.com.
>
> =====================
> To manage your subscription to SPSSX-L, send a message to
> LISTSERV@...
> (not to SPSSX-L), with no body text except the
> command. To leave the list, send the command
> SIGNOFF SPSSX-L
> For a list of commands to manage subscriptions, send the
> command
> INFO REFCARD
>

=====================
To manage your subscription to SPSSX-L, send a message to
LISTSERV@... (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Richard Ristow]

In a posting that I didn't receive, eins wrote,

A variable (which is "email address" of 10,000 persons) is in one column of the SPSS. I want to produce output such that the email adds would be separated by comma. For example, suppose the email adds of the five persons are:

smith@...
john@...
wyatt@...
watson@...
briggs@...

The desired output would be:

smith@..., john@..., wyatt@..., watson@..., briggs@...

So you want one record with 10,000 values concatenated, separated by commas? For almost all uses, that's a very clumsy representation, especially in SPSS.

However, here's a native SPSS solution. (You can't use FLIP, because it works only with numeric variables):

|-----------------------------|---------------------------|
|Output Created               |23-OCT-2009 16:23:57       |
|-----------------------------|---------------------------|
E_Address

smith@...
john@...
wyatt@...
watson@...
briggs@...

Number of cases read:  5    Number of cases listed:  5

COMPUTE NoBreak = 1.
ADD FILES
   /FILE=*
   /BY   NoBreak
   /LAST = LastOne.

FORMATS NoBreak   LastOne   (F2).
STRING  AllOfThem          (A60).
LEAVE   AllOfThem.

DO IF   AllOfThem EQ ' '.
.  COMPUTE AllOfThem = E_Address.
ELSE.
.  COMPUTE AllOfThem = CONCAT(RTRIM(AllOfThem),',',E_Address).
END IF.

.  /**/  LIST  /*-*/.

|-----------------------------|---------------------------|
|Output Created               |23-OCT-2009 16:23:57       |
|-----------------------------|---------------------------|
The variables are listed in the following order:

LINE   1: E_Address NoBreak LastOne
LINE   2: AllOfThem

   E_Address: smith@...   1  0
   AllOfThem: smith@...

   E_Address: john@...    1  0
   AllOfThem: smith@...,john@...

   E_Address: wyatt@...   1  0
   AllOfThem: smith@...,john@...,wyatt@...

   E_Address: watson@...  1  0
   AllOfThem: smith@...,john@...,wyatt@...,watson@yahoo.

   E_Address: briggs@...  1  1
   AllOfThem: smith@...,john@...,wyatt@...,watson@yahoo.


Number of cases read:  5    Number of cases listed:  5

SELECT IF LastOne.
ADD FILES /FILE=*
  DROP=E_Address NoBreak LastOne.
LIST.
|-----------------------------|---------------------------|
|Output Created               |23-OCT-2009 16:23:58       |
|-----------------------------|---------------------------|
AllOfThem

smith@...,john@...,wyatt@...,watson@yahoo.

Number of cases read:  1    Number of cases listed:  1
=============================
APPENDIX: Test data, and code
=============================
DATA LIST FREE / E_Address (A16).
BEGIN DATA
smith@...
john@...
wyatt@...
watson@...
briggs@...
END DATA.
LIST.

COMPUTE NoBreak = 1.
ADD FILES
   /FILE=*
   /BY   NoBreak
   /LAST = LastOne.
  
FORMATS NoBreak   LastOne   (F2).
STRING  AllOfThem          (A60).
LEAVE   AllOfThem.

DO IF   AllOfThem EQ ' '.
.  COMPUTE AllOfThem = E_Address.
ELSE.
.  COMPUTE AllOfThem = CONCAT(RTRIM(AllOfThem),',',E_Address).
END IF.

.  /**/  LIST  /*-*/.

SELECT IF LastOne.
ADD FILES /FILE=*
  DROP=E_Address NoBreak LastOne.
LIST.
===================== To manage your subscription to SPSSX-L, send a message to LISTSERV@... (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

[Martins Liberts]

I did not realise that FLIP will fail. OK there is another PASW native solution using CASESTOVARS.

*****************.

data list free
 /email (a50).
begin data
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
end data.

comp id=$casenum.
sort cases id (d).
comp max=id+$casenum-1.
sort cases id.

if id<max email=con(rtr(email),",").

CASESTOVARS
 /ID=max
 /drop id max.

alter type all (amin).

print out "c:\data\temp\emails.txt"
 /all.

exe.

Richard Ristow wrote:

In a posting that I didn't receive, eins wrote,
A variable (which is "email
address" of 10,000 persons) is in one column of the SPSS. I want to
produce output such that the email adds would be separated by comma. For
example, suppose the email adds of the five persons are:

[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
The desired output would be:
[e-mail], [e-mail], [e-mail], [e-mail],
[e-mail]
So you want one record with 10,000 values concatenated, separated by
commas? For almost all uses, that's a very clumsy representation,
especially in SPSS.
However, here's a native SPSS solution. (You can't use FLIP, because it
works only with numeric variables):

|-----------------------------|---------------------------|
|Output
Created              
|23-OCT-2009 16:23:57       |
|-----------------------------|---------------------------|
E_Address
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
Number of cases read:  5    Number of cases
listed:  5
COMPUTE NoBreak = 1.
ADD FILES
   /FILE=*
   /BY   NoBreak
   /LAST = LastOne.
FORMATS NoBreak   LastOne   (F2).
STRING 
AllOfThem         
(A60).
LEAVE   AllOfThem.
DO IF   AllOfThem EQ ' '.
.  COMPUTE AllOfThem = E_Address.
ELSE.
.  COMPUTE AllOfThem = CONCAT(RTRIM(AllOfThem),',',E_Address).
END IF.
.  /**/  LIST  /*-*/.
|-----------------------------|---------------------------|
|Output
Created              
|23-OCT-2009 16:23:57       |
|-----------------------------|---------------------------|
The variables are listed in the following order:
LINE   1: E_Address NoBreak LastOne
LINE   2: AllOfThem
   E_Address: [e-mail]   1  0
   AllOfThem: [e-mail]
   E_Address: [e-mail]    1  0
   AllOfThem: [e-mail],[e-mail]
   E_Address: [e-mail]   1  0
   AllOfThem:
[e-mail],[e-mail],[e-mail]
   E_Address: [e-mail]  1  0
   AllOfThem:
[e-mail],[e-mail],[e-mail],watson@yahoo.
   E_Address: [e-mail]  1  1
   AllOfThem:
[e-mail],[e-mail],[e-mail],watson@yahoo.

Number of cases read:  5    Number of cases
listed:  5
SELECT IF LastOne.
ADD FILES /FILE=*
  DROP=E_Address NoBreak LastOne.
LIST.
|-----------------------------|---------------------------|
|Output
Created              
|23-OCT-2009 16:23:58       |
|-----------------------------|---------------------------|
AllOfThem
[e-mail],[e-mail],[e-mail],watson@yahoo.
Number of cases read:  1    Number of cases
listed:  1
=============================
APPENDIX: Test data, and code
=============================
DATA LIST FREE / E_Address (A16).
BEGIN DATA
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
END DATA.
LIST.
COMPUTE NoBreak = 1.
ADD FILES
   /FILE=*
   /BY   NoBreak
   /LAST = LastOne.
  
FORMATS NoBreak   LastOne   (F2).
STRING 
AllOfThem         
(A60).
LEAVE   AllOfThem.
DO IF   AllOfThem EQ ' '.
.  COMPUTE AllOfThem = E_Address.
ELSE.
.  COMPUTE AllOfThem = CONCAT(RTRIM(AllOfThem),',',E_Address).
END IF.
.  /**/  LIST  /*-*/.
SELECT IF LastOne.
ADD FILES /FILE=*
  DROP=E_Address NoBreak LastOne.
LIST.



=====================
To manage your subscription to SPSSX-L, send a message to
[e-mail] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Martins Liberts]

Ups! Another solution avoiding double sorting ;)

****************.

data list free
 /email (a50).
begin data
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
end data.

comp const=1.

agg out * mode=add
 /break const
 /ncases=n.

if $casenum<ncases email=con(rtr(email),",").

CASESTOVARS
 /ID=const
 /drop const ncases.

alter type all (amin).

print out "c:\data\temp\emails.txt"
 /all.

exe.






I did not realise that FLIP will fail. OK there is another PASW native solution using CASESTOVARS.

*****************.

data list free
 /email (a50).
begin data
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
end data.

comp id=$casenum.
sort cases id (d).
comp max=id+$casenum-1.
rt caseses id.

if id<max email=con(rtr(ema),",").).

CASESTOVARS
 /ID=max
 /drop id max.

alter type all (amin).

print out "c:\data\temp\emails.txt"
 /all.

exe.

Richard Ristow wrote:

In a posting that I didn't receive, eins wrote,
A variable (which is "email
address" of 10,000 pens)  is s in one column of the SPSS. I want to
produce output such that the email adds would be separated by comma. For
example, suppose the email adds of the five persons are:

[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
The desired output would be:
[e-mail], [e-mail], [e-mail], [e-mail],
[e-mail]
So you want one record with 10,000 values concatenated, separated by
commas? For almost all uses, that's a very clumsy representation,
especially in SPSS.
However, here's a native SPSS solution. (You can't use FLIP, because it
works only with numeric variables):

|-----------------------------|---------------------------|
|Output
Created              
|23-OCT-2009 16:23:57       |
|-----------------------------|---------------------------|
E_Address
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
Number of cases read:  5    Number of cases
listed:  5
COMPUTE NoBreak = 1.
ADD FILES
   /FILE=*
   /BY   NoBreak
   /LAST = LastOne.
FORMATS NoBreak   LastOne   (F2).
STRING 
AllOfThem         
(A60).
LEAVE   AllOfThem.
DO IF   AllOfThem EQ ' '.
.  COMPUTE AllOfThem = E_Address.
ELSE.
.  COMPUTE AllOfThem = CONCAT(RTRIM(AllOfThem),',',E_Address).
END IF.
.  /**/  LIST  /*-*/.
|-----------------------------|---------------------------|
|Output
Created              
|23-OCT-2009 16:23:57       |
|-----------------------------|---------------------------|
The variables are listed in the following order:
LINE   1: E_Address NoBreak LastOne
LINE   2: AllOfThem
   E_Address: [e-mail]   1  0
   AllOfThem: [e-mail]
   E_Address: [e-mail]    1  0
   AllOfThem: [e-mail],[e-mail]
   E_Address: [e-mail]   1  0
   AllOfThem:
[e-mail],[e-mail],[e-mail]
   E_Address: [e-mail]  1  0
   AllOfThem:
[e-mail],[e-mail],[e-mail],watson@yahoo.
   E_Address: [e-mail]  1  1
   AllOfThem:
[e-mail],[e-mail],[e-mail],watson@yahoo.

Number of cases read:  5    Number of cases
listed:  5
SELECT IF LastOne.
ADD FILES /FILE=*
  DROP=E_Address NoBreak LastOne.
LIST.
|-----------------------------|---------------------------|
|Output
Created              
|23-OCT-2009 16:23:58       |
|-----------------------------|---------------------------|
AllOfThem
[e-mail],[e-mail],[e-mail],watson@yahoo.
Number of cases read:  1    Number of cases
listed:  1
=============================
APPENDIX: Test data, and code
=============================
DATA LIST FREE / E_Address (A16).
BEGIN DATA
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
END DATA.
LIST.
COMPUTE NoBreak = 1.
ADD FILES
   /FILE=*
   /BY   NoBreak
   /LAST = LastOne.
  
FORMATS NoBreak   LastOne   (F2).
STRING 
AllOfThem         
(A60).
LEAVE   AllOfThem.
DO IF   AllOfThem EQ ' '.
.  COMPUTE AllOfThem = E_Address.
ELSE.
.  COMPUTE AllOfThem = CONCAT(RTRIM(AllOfThem),',',E_Address).
END IF.
.  /**/  LIST  /*-*/.
SELECT IF LastOne.
ADD FILES /FILE=*
  DROP=E_Address NoBreak LastOne.
LIST.



=====================
To manage your subscription to SPSSX-L, send a message to
[e-mail] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Gene Maguin]

Martin,

I've been watching from the sidelines (peanut gallery) of your posting but
I'd like to ask a question.

Can a concantenated list of email addresses include blank spaces? I ask
because when I first saw your posting I was thinking that you needed a
comma-separated, concantenated list, which would be a single variable and,
as you know, such a variable is limited to about 32K (Western language)
characters. So only about 1000-1500 addresses.

Gene Maguin

=====================
To manage your subscription to SPSSX-L, send a message to
LISTSERV@... (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Martins Liberts]

Sorry, I did not get your question...

I am not concatenating all email addresses in one variable. I am creating separate variable for each email.


Martins

Gene Maguin wrote:

Martin,

I've been watching from the sidelines (peanut gallery) of your posting but
I'd like to ask a question.

Can a concantenated list of email addresses include blank spaces? I ask
because when I first saw your posting I was thinking that you needed a
comma-separated, concantenated list, which would be a single variable and,
as you know, such a variable is limited to about 32K (Western language)
characters. So only about 1000-1500 addresses.

Gene Maguin

=====================
To manage your subscription to SPSSX-L, send a message to
[e-mail] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Gene Maguin]

Martin,

I misunderstood what you needed to do. I was visualizing a comma or
semicolon-delimited, concantenated list of addresses such as when you send
an email to a list of persons.

Gene Maguin

=====================
To manage your subscription to SPSSX-L, send a message to
LISTSERV@... (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Richard Ristow]

At 09:34 AM 10/26/2009, Martins Liberts wrote:

>I am not concatenating all email addresses in one variable. I am
>creating separate variable for each email.

Understood. But the original posting, as you quoted it, requested,

>>The desired output would be:
>>
>>smith@..., john@..., wyatt@...,
>>watson@...,briggs@...

=====================
To manage your subscription to SPSSX-L, send a message to
LISTSERV@... (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Martins Liberts]

The original request is "I want to produce output such that the email adds would be separated by comma.". The output is not specified in the request. So I decided that possible output could be a text file. It would be easy to use for emailing. So in my solution output is saved in a text file.

print out "c:\data\temp\emails.txt"
 /all.


HTH

Richard Ristow wrote:

At 09:34 AM 10/26/2009, Martins Liberts wrote:

>I am not concatenating all email addresses in one variable. I am
>creating separate variable for each email.

Understood. But the original posting, as you quoted it, requested,

>>The desired output would be:
>>
>>[e-mail], [e-mail], [e-mail],
>>[e-mail],[e-mail]

=====================
To manage your subscription to SPSSX-L, send a message to
[e-mail] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD