ERROR: Function SUBSTR requires a character expression as argument 1. and adding zeroes in front of-CodePudding

My end goal is to add zeroes in front of my data, so 918 becomes 0918 and 10 becomes 0010 limited at 4 characters. My solution so far is to use SUBSTR like i do below:

PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_DAGLIGEKORREKTION_0000 AS 
   SELECT (SUBSTR(line_item, 1, 4)) AS line_item, 
          (SUBSTR(column_item, 1, 4)) AS column_item
 
      FROM QUERY_FOR_DAGLIGEKORREKTIONER t1;
QUIT;

But when I run my query I get the following error:

ERROR: Function SUBSTR requires a character expression as argument 1.
ERROR: Function SUBSTR requires a character expression as argument 1.

This is my data set:

line_item   column_item     
918         10
230         10
260         10
918         10
918         10
918         10
70          10
80          10
110         10
250         10
35          10

What am I doing wrong? and is there another maybe easier way to add zeroes in fornt of my data? I hope you can lead me in the right direction.

CodePudding user response：

I found a solution by searching to something similar to the Oracle solution by "d r and I found the following solution to the problem:

  put(line_item, z4.) AS PAD_line_item,
  put(column_item, z4.) AS PAD_column_item,

resulting in:

line_item   column_item
    0918    0010
    0230    0010
    0260    0010
    0918    0010
    0918    0010
    0918    0010
    0070    0010
    0080    0010
    0110    0010
    0250    0010
    0035    0010

I hope this will help someone in the future with leading zeroes.

CodePudding user response：

In SAS you can associate a format with a numeric variable to specify how the value is rendered when output in a report or displayed in a query result.

Example:

Specify a column to be displayed using the Z<n>. format.

  select <numeric-var> format=z4.

The underlying column is still numeric.

If you want to convert the numeric result permanently to a character type, use the PUT function.

  select PUT(<numeric-expression>, Z4.) as <column-name>

CodePudding user response：

Oracle

Select
    LPAD(1, 4, '0') "A",
    LPAD(12, 4, '0') "B",
    LPAD(123, 4, '0') "C",
    LPAD(1234, 4, '0') "D",
    LPAD(12345, 4, '0') "E"
From Dual
--
--    R e s u l t
--    
--    A    B    C    D    E  
--  ---- ---- ---- ---- ----
--  0001 0012 0123 1234 1234

CodePudding user response：

Add the value to 10,000; Cast the result to a VARCHAR(5) (or longer); Get SUBSTR(2,4) out of that.

SELECT
  SUBSTR((line_item     10000)::VARCHAR(5),2,4) AS s_line_item
, SUBSTR((column_item   10000)::VARCHAR(5),2,4) AS s_column_item
FROM indata;
-- out  s_line_item | s_column_item 
-- out ------------- ---------------
-- out  0918        | 0010
-- out  0230        | 0010
-- out  0260        | 0010
-- out  0918        | 0010
-- out  0918        | 0010
-- out  0918        | 0010
-- out  0070        | 0010
-- out  0080        | 0010
-- out  0110        | 0010
-- out  0250        | 0010
-- out  0035        | 0010