Numeric to Charecter from VA Interface

[znhnm]

Hello,

I have a dataset that has an ID column but some records are 4 digits and some are 5 on this column. I would like to add a 0 as the first digit when there are 4 digits in the record. Can I do that from VA interface? The variable type of the ID column is a mesure and I created a category variable changing the classification. I tried to use the Calculated Item and GetLength operator but since my column is not a string I cannot apply that operator. So, my question is how can I change a numeric variable to a string variable so that I can count the number of digits and add a 0 in the beginning when there are 4 digits, from VA interface?

If it is too complicated I would go to SAS Studio to write code but I don't want to do that because I would need to recreate my table with the new dataset because I have some calculations I have done previously. 

Thanks! 

[HunterT_SAS]

Someone else may have a better idea but one way you can achieve this is to do the following:

1. Use the Format function in a calculated item to create your categorical ID value. I also found that I needed to include the RemoveBlanks function because the format applied will pad the value with extra blank spaces and mess up the GetLength bit later but here is my example where ID is my column and I set the format with BEST5. I called this: "ID (String)":

RemoveBlanks(Format('ID'n, 'BEST5.'), _All_)

2. Create a new calculated item to append (concatenate function) the 0 when the length is equal to 4. I called this "New ID":

IF ( GetLength('ID (String)'n) = 4 )
RETURN Concatenate('0', 'ID (String)'n)
ELSE 'ID (String)'n

And then this looks like the following in a list table:

You could also combine the two calculations into one calculation if you wanted. Hopefully that helps!

[ballardw]

Does VA not support the Z format to provide leading zeroes?

I have never had access to VA so don't know which formats are supported.

---

@HunterT_SAS wrote:

Someone else may have a better idea but one way you can achieve this is to do the following:

1. Use the Format function in a calculated item to create your categorical ID value. I also found that I needed to include the RemoveBlanks function because the format applied will pad the value with extra blank spaces and mess up the GetLength bit later but here is my example where ID is my column and I set the format with BEST5. I called this: "ID (String)":

RemoveBlanks(Format('ID'n, 'BEST5.'), _All_)

2. Create a new calculated item to append (concatenate function) the 0 when the length is equal to 4. I called this "New ID":

IF ( GetLength('ID (String)'n) = 4 )
RETURN Concatenate('0', 'ID (String)'n)
ELSE 'ID (String)'n

And then this looks like the following in a list table:

You could also combine the two calculations into one calculation if you wanted. Hopefully that helps!

---

[HunterT_SAS]

There is that better idea I was looking for. The Z format is indeed available with the Format function, and does exactly that. All you need to do then is create a single calculated item using the Format function to convert this to a category, use Z, and you're done:

Thanks @ballardw for that info!

[SASKiwi]

ID-type columns should always be defined as character to begin with and then you would not have any problem with leading zeros being dropped.

I also suggest that it is best practice to prepare your VA data prior to loading to your exact requirements, defining ID as character, rather than fixing it once it is loaded into memory.