select a numeric column as string

user_11363714 0
Viewed 1104

Is it possible to select a numeric column as string with proc sql in sas ?

for exemple i have this table (table_1):

+------+------+------+--------------+--+
| id_1 | id_2 | id_3 |     date     |  |
+------+------+------+--------------+--+
|    3 |    7 |    3 |  25/06/2017  |  |
|    4 |   11 |    9 |  25/06/2020  |  |
+------+------+------+--------------+--+

id_1, id_2 and id_3 can be numeric or string.

i want to create a table (table_2) where these three columns should have string as type

I wrote this code :

proc sql;

Create table table_2 as 

select date, Convert(varchar(30),a.id_1), Convert(varchar(30),a.id_2), Convert(varchar(30),a.id_3)

from table_1   a

;quit;

But it doesn't works

sas proc converters sql
2 Answers

The best solution is to fix your up stream processes to always generate the variables using a consistent type.

In SAS you can use the PUT() function to convert a value to a string, just use a FORMAT that is appropriate for the type of the variable. For example if your variable is number but it should have been 9 digits long with significant leading zeros then you would want to use the Z9. format to convert it and have the leading zeros represented.

 select put(id_1,z9.) as id_1

If you want to convert either a number or a character variable to a string without first knowing the type of the variable you could use the CATS() function. But then you will not have any control over how the numbers are converted into strings. Use the LENGTH= attribute to force SAS to define the variable with a length of 30.

 select cats(id_1) as id_1 length=30
user_49655490

Just Try It ...

SELECT date, CAST(a.id_1 AS varchar(30)), CAST(a.id_2 AS varchar(30)), CAST(a.id_3 AS varchar(30))
FROM table_1   a
user_92835400
Related