1

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.

Christoffer
  • 326
  • 1
  • 4
  • 20
  • Is your variable numeric or character? You are trying to use a character function, SUBSTR(), on a numeric variable. To convert a number to a string use the PUT() function with an appropriate format. The format that generates leading zeros is the Z format. – Tom Jul 29 '22 at 13:13

4 Answers4

4

I found a solution by searching for 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.

Christoffer
  • 326
  • 1
  • 4
  • 20
4

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>
Richard
  • 25,390
  • 3
  • 25
  • 38
2

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
d r
  • 3,848
  • 2
  • 4
  • 15
  • Thank you for leading me in the right direction! would it be possible to use Oracle syntax in SAS? – Christoffer Jul 29 '22 at 11:14
  • 1
    Sorry, but I know nothing about sas. I am old school Oracle guy. Thing that I know is that SQL in different contexts has sintax similar enough to be compareable / informative / adwiceable / and above all useable. Glad to know that you get the result in sas reading Oracle sql. Regards... – d r Jul 29 '22 at 11:23
0

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
marcothesane
  • 6,192
  • 1
  • 11
  • 21
  • I was unable to get this to work it resulted in the following error: ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=. – Christoffer Jul 29 '22 at 11:12
  • 1
    Where in my sql code did you exactly find the error? It looks like SQL all right, for me - and it works in all DBMS-s I tried. I don't have access to a SAS server, though, but I thought it speaks a decent SQL, too ... – marcothesane Jul 29 '22 at 11:19
  • 1
    Try, one after the other: `SELECT line_item + 10000 FROM QUERY_FOR_DAGLIGEKORREKTIONER t1` . Then: `SELECT (line_item + 10000)::VARCHAR(5) FROM QUERY_FOR_DAGLIGEKORREKTIONER t1` . Finally: `SELECT SUBSTR(line_item + 10000)::VARCHAR(5),2,4) FROM QUERY_FOR_DAGLIGEKORREKTIONER t1` You will find out where the error comes up. – marcothesane Jul 29 '22 at 11:28
  • Great way to debug, 'line_item + 10000' worked but the second one failed: '(line_item + 10000)::VARCHAR(5)' – Christoffer Jul 29 '22 at 12:19
  • 1
    Looks like you can't cast numbers to integers using the double-colon operator. Does `CAST(line_item + 10000 AS VARCHAR(5))` work? Found it - as well as the double colon - in the SAS docu: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.3/fedsqlref/n1fxzvj0otejpin15l3litfv4q62.htm – marcothesane Jul 29 '22 at 12:40
  • Why would you use SUBSTR() on a numeric value? – Tom Jul 29 '22 at 13:15
  • Remember, we want to left-pad a numeric value with zeroes. In all DBMS-s I've encountered, the fastest way is to add ten to the power of the number of digits to left-pad to, to the base number; cast that to a string; and get substring of that result, from position 2 to a length of the digit count. Left-padding with '0' is a nested loop with a string operation, and that tends to be expensive. – marcothesane Jul 29 '22 at 13:21
  • 1
    This is not a good solution for SAS. Also, FEDSQL is not the same as what the OP is using here (FEDSQL is SAS's ANSI SQL compliant variant that's intended for certain kinds of work that are uncommon.) – Joe Jul 29 '22 at 20:41