Adding leading characters to ID

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Adding leading characters to ID

Jake Gross-2
SPSS Folks,

I am trying to merge two files using a common case ID that have been stored in different formats in each file (see example below). The formats are both string variables. All of the IDs in file 1 have 1 or 2 leading zeroes and are all six characters in length. File 2 has ID's with no leading zeroes and each ID number if 4 or 5 characters. I need to add leading zeroes (as strings) to the second file, with 1 or 2 zeroes added depending on the current length of the string. Any help is appreciated.

Best,
Jake

CURRENT DATA Structure

FILE 1 ID - 001234
FILE 2 ID - 1234

DESIRED DATA Structure

FILE 1 ID - 001234
FILE 2 ID - 001234

data list list /str1(a6).
begin data
12345
2345
45625
36789
end data.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (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
Reply | Threaded
Open this post in threaded view
|

Re: Adding leading characters to ID

Maguin, Eugene
Time to get familiar with the string functions section of the syntax reference (Universals, transformation expressions, string functions).

File 1 id is a6 format; file 2 id is a4 format.
* Using file 2.
Alter type id(a6).
* looks like id could be either 4 or 5 characters wide. I presume id has no leading blanks. Very important.
Do if (char.length(id) eq 4).
+  compute id=concat('00',rtrim(id)).
Else if (char.length(id) eq 5).
+  compute id=concat('0',rtrim(id)).
End if.


-----Original Message-----
From: SPSSX(r) Discussion <[hidden email]> On Behalf Of Jake Gross
Sent: Tuesday, April 10, 2018 2:11 PM
To: [hidden email]
Subject: Adding leading characters to ID

SPSS Folks,

I am trying to merge two files using a common case ID that have been stored in different formats in each file (see example below). The formats are both string variables. All of the IDs in file 1 have 1 or 2 leading zeroes and are all six characters in length. File 2 has ID's with no leading zeroes and each ID number if 4 or 5 characters. I need to add leading zeroes (as strings) to the second file, with 1 or 2 zeroes added depending on the current length of the string. Any help is appreciated.

Best,
Jake

CURRENT DATA Structure

FILE 1 ID - 001234
FILE 2 ID - 1234

DESIRED DATA Structure

FILE 1 ID - 001234
FILE 2 ID - 001234

data list list /str1(a6).
begin data
12345
2345
45625
36789
end data.

=====================
To manage your subscription to SPSSX-L, send a message to [hidden email] (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
[hidden email] (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
Reply | Threaded
Open this post in threaded view
|

Re: Adding leading characters to ID

Ives, Melissa L
Or just us CHAR.LPAD.

As in:
String ID (A6).
compute ID=CHAR.LPAD(str1,6,'0').

CHAR.LPAD. CHAR.LPAD(strexpr1,length[,strexpr2]). String. Left-pads strexpr1 to make its length the value specified by length using as many complete copies as will fit of strexpr2 as the padding string.
The value of length represents the number of characters and must be a positive integer. If the optional argument strexpr2 is omitted, the value is padded with blank spaces.
Melissa

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Maguin, Eugene
Sent: Tuesday, April 10, 2018 2:27 PM
To: [hidden email]
Subject: Re: [SPSSX-L] Adding leading characters to ID

Time to get familiar with the string functions section of the syntax reference (Universals, transformation expressions, string functions).

File 1 id is a6 format; file 2 id is a4 format.
* Using file 2.
Alter type id(a6).
* looks like id could be either 4 or 5 characters wide. I presume id has no leading blanks. Very important.
Do if (char.length(id) eq 4).
+  compute id=concat('00',rtrim(id)).
Else if (char.length(id) eq 5).
+  compute id=concat('0',rtrim(id)).
End if.


-----Original Message-----
From: SPSSX(r) Discussion <[hidden email]> On Behalf Of Jake Gross
Sent: Tuesday, April 10, 2018 2:11 PM
To: [hidden email]
Subject: Adding leading characters to ID

SPSS Folks,

I am trying to merge two files using a common case ID that have been stored in different formats in each file (see example below). The formats are both string variables. All of the IDs in file 1 have 1 or 2 leading zeroes and are all six characters in length. File 2 has ID's with no leading zeroes and each ID number if 4 or 5 characters. I need to add leading zeroes (as strings) to the second file, with 1 or 2 zeroes added depending on the current length of the string. Any help is appreciated.

Best,
Jake

CURRENT DATA Structure

FILE 1 ID - 001234
FILE 2 ID - 1234

DESIRED DATA Structure

FILE 1 ID - 001234
FILE 2 ID - 001234

data list list /str1(a6).
begin data
12345
2345
45625
36789
end data.

=====================
To manage your subscription to SPSSX-L, send a message to [hidden email] (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 [hidden email] (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

________________________________

This correspondence contains proprietary information some or all of which may be legally privileged; it is for the intended recipient only. If you are not the intended recipient you must not use, disclose, distribute, copy, print, or rely on this correspondence and completely dispose of the correspondence immediately. Please notify the sender if you have received this email in error. NOTE: Messages to or from the State of Connecticut domain may be subject to the Freedom of Information statutes and regulations.


=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (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
Reply | Threaded
Open this post in threaded view
|

Re: Adding leading characters to ID

Maguin, Eugene
Yes! Even better. Guess I need to study that section as well.

-----Original Message-----
From: SPSSX(r) Discussion <[hidden email]> On Behalf Of Ives, Melissa L
Sent: Tuesday, April 10, 2018 3:10 PM
To: [hidden email]
Subject: Re: Adding leading characters to ID

Or just us CHAR.LPAD.

As in:
String ID (A6).
compute ID=CHAR.LPAD(str1,6,'0').

CHAR.LPAD. CHAR.LPAD(strexpr1,length[,strexpr2]). String. Left-pads strexpr1 to make its length the value specified by length using as many complete copies as will fit of strexpr2 as the padding string.
The value of length represents the number of characters and must be a positive integer. If the optional argument strexpr2 is omitted, the value is padded with blank spaces.
Melissa

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Maguin, Eugene
Sent: Tuesday, April 10, 2018 2:27 PM
To: [hidden email]
Subject: Re: [SPSSX-L] Adding leading characters to ID

Time to get familiar with the string functions section of the syntax reference (Universals, transformation expressions, string functions).

File 1 id is a6 format; file 2 id is a4 format.
* Using file 2.
Alter type id(a6).
* looks like id could be either 4 or 5 characters wide. I presume id has no leading blanks. Very important.
Do if (char.length(id) eq 4).
+  compute id=concat('00',rtrim(id)).
Else if (char.length(id) eq 5).
+  compute id=concat('0',rtrim(id)).
End if.


-----Original Message-----
From: SPSSX(r) Discussion <[hidden email]> On Behalf Of Jake Gross
Sent: Tuesday, April 10, 2018 2:11 PM
To: [hidden email]
Subject: Adding leading characters to ID

SPSS Folks,

I am trying to merge two files using a common case ID that have been stored in different formats in each file (see example below). The formats are both string variables. All of the IDs in file 1 have 1 or 2 leading zeroes and are all six characters in length. File 2 has ID's with no leading zeroes and each ID number if 4 or 5 characters. I need to add leading zeroes (as strings) to the second file, with 1 or 2 zeroes added depending on the current length of the string. Any help is appreciated.

Best,
Jake

CURRENT DATA Structure

FILE 1 ID - 001234
FILE 2 ID - 1234

DESIRED DATA Structure

FILE 1 ID - 001234
FILE 2 ID - 001234

data list list /str1(a6).
begin data
12345
2345
45625
36789
end data.

=====================
To manage your subscription to SPSSX-L, send a message to [hidden email] (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 [hidden email] (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

________________________________

This correspondence contains proprietary information some or all of which may be legally privileged; it is for the intended recipient only. If you are not the intended recipient you must not use, disclose, distribute, copy, print, or rely on this correspondence and completely dispose of the correspondence immediately. Please notify the sender if you have received this email in error. NOTE: Messages to or from the State of Connecticut domain may be subject to the Freedom of Information statutes and regulations.


=====================
To manage your subscription to SPSSX-L, send a message to [hidden email] (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
[hidden email] (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
Reply | Threaded
Open this post in threaded view
|

SV: Adding leading characters to ID

Robert L
In reply to this post by Jake Gross-2
I would use conversion of strings to a numeric (F) format, followed by altering type of both ID variables to N6.

DATA LIST LIST /str1(a6) str2(a4).
BEGIN DATA
012345 2
002345 23
045625 345
036789 1234
END DATA.

COMPUTE id1=NUMBER(str1,F6).
COMPUTE id2=NUMBER(str2,F6) /*could be F4 just as well*/.
EXECUTE.

ALTER TYPE id1 id2(N6).

-----Ursprungligt meddelande-----
Från: SPSSX(r) Discussion [mailto:[hidden email]] För Jake Gross
Skickat: den 10 april 2018 20:11
Till: [hidden email]
Ämne: Adding leading characters to ID

SPSS Folks,

I am trying to merge two files using a common case ID that have been stored in different formats in each file (see example below). The formats are both string variables. All of the IDs in file 1 have 1 or 2 leading zeroes and are all six characters in length. File 2 has ID's with no leading zeroes and each ID number if 4 or 5 characters. I need to add leading zeroes (as strings) to the second file, with 1 or 2 zeroes added depending on the current length of the string. Any help is appreciated.

Best,
Jake

CURRENT DATA Structure

FILE 1 ID - 001234
FILE 2 ID - 1234

DESIRED DATA Structure

FILE 1 ID - 001234
FILE 2 ID - 001234

data list list /str1(a6).
begin data
12345
2345
45625
36789
end data.

=====================
To manage your subscription to SPSSX-L, send a message to [hidden email] (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
[hidden email] (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
Robert Lundqvist