topic Re: Numeric to character in SAS Programming

Numeric to character

bhca60 — Fri, 09 Jun 2023 17:10:38 GMT

I am using SAS VIYA and trying to convert a variable “ID” (data from an excel import) from numeric to character. I am trying to join it to the “ID” in another data set to bring back any matches.

I’ve done this but it brings nothing back and want to make sure if that’s because there are no matches or because something is wrong with my input:

Data c;
Set b;
ID_char=put(ID,8.);
Run;

Re: Numeric to character

Kurt_Bremser — Fri, 09 Jun 2023 17:14:17 GMT

Hoe do the codes in the other dataset with the character variable look like?

Re: Numeric to character

bhca60 — Fri, 09 Jun 2023 17:21:11 GMT

The ID is a six digit number 123456

Re: Numeric to character

Reeza — Fri, 09 Jun 2023 17:29:16 GMT

Try adding the -l component and trim in case you have blanks?

ID_Char = put(trim(ID), 8. -l);

(PS not that familiar with Viya)

Re: Numeric to character

Kurt_Bremser — Fri, 09 Jun 2023 17:41:06 GMT

Then use the Z6. format instead of 8.

Re: Numeric to character

bhca60 — Fri, 09 Jun 2023 17:45:11 GMT

It looks like it worked but there is an ID that has a leading zero like 099999 but still shows as 99999.

Re: Numeric to character

ballardw — Fri, 09 Jun 2023 17:47:48 GMT

@bhca60 wrote:
It looks like it worked but there is an ID that has a leading zero like 099999 but still shows as 99999.

Shows where?

If you send values to a spreadsheet quite often the rules of the spreadsheet will convert text containing digits to numeric which loses the leading zeros.

Re: Numeric to character

Kurt_Bremser — Fri, 09 Jun 2023 17:50:07 GMT

That's why you need to use the Z format, which prints leading zeroes.

Re: Numeric to character

bhca60 — Fri, 09 Jun 2023 17:52:11 GMT

Right but I imported it to sas from excel and thought that it would include a 0 in the output after conversion to character.

Re: Numeric to character

Kurt_Bremser — Fri, 09 Jun 2023 17:55:17 GMT

Numbers do not have leading zeroes. You must force the display of leading zeroes by using the proper format during the conversion to character.

Re: Numeric to character

Reeza — Fri, 09 Jun 2023 18:53:19 GMT

Then you can use the Z6 format in your conversion.

ID_char = put(trim(ID), z6. -l);

Re: Numeric to character

bhca60 — Fri, 09 Jun 2023 19:12:11 GMT

And what if you want to remove leading zeros but keep it a character?

Re: Numeric to character

Tom — Fri, 09 Jun 2023 19:18:00 GMT

@Reeza wrote:

Then you can use the Z6 format in your conversion.
ID_char = put(trim(ID), z6. -l);

Do not use the TRIM() function with a NUMERIC variable.

There is no need to use the -L modifier with the Z format as there will not be any leading spaces to remove.

ID_char = put(ID, z6.);

Re: Numeric to character

Reeza — Fri, 09 Jun 2023 19:18:01 GMT

ID_char = put(input(id_char, 8.), 8. -l)

Re: Numeric to character

SASKiwi — Sat, 10 Jun 2023 22:37:16 GMT

@bhca60 - It is normally better to fix this problem in Excel and then re-import. Open Excel, select the ID column. In the Data tab, select the Text to Column wizard, and go through the dialog, selecting Text as your column type. Now, when you re-import into SAS your ID column type will stay as character.