topic Re: How do I pad a character variable with leading zeroes using PROC SQL in SAS Programming

How do I pad a character variable with leading zeroes using PROC SQL

MattSan — Tue, 25 Oct 2016 08:23:36 GMT

Hello,

I have a dataset that contains various numbers stored in a character format i.e.

123456

7890

I would like to pad each of these values to so that each string is 16 characters long using a PROC SQL statement i.e.

0000000000123456

0000000000007890

0000000000000023

This is what I have tried so far without much success:

PROC SQL;

SELECT FORMAT (X, '0000000000000000000')

FROM Table;

QUIT;

Any assistance would be greatly appreciated.

Re: How do I pad a character variable with leading zeroes using PROC SQL

RW9 — Tue, 25 Oct 2016 08:33:10 GMT

There are many ways, concatenation, changing to number and then back again are two for example:

data want;
  a="123456";
  b=cats(repeat("0",20-lengthn(a)),a);
  c=put(input(a,best.),z20.);
run;

Re: How do I pad a character variable with leading zeroes using PROC SQL

MattSan — Tue, 25 Oct 2016 08:46:07 GMT

Thank you however I was looking for a PROC SQL solution if possible? I need a PROC SQL version in order to possibly increase the processing speed on a large dataset.

Re: How do I pad a character variable with leading zeroes using PROC SQL

RW9 — Tue, 25 Oct 2016 08:44:23 GMT

Firstly, have you hard evidence that using proc sql will increase the processing speed? I can certainly pull out some posts which show SQL usage on large datasets to be far more resource consuming. Never restrict yourself to one technology.

Secondly, most datastep statement can be moved into a proc sql - of course if its passthrough then its a different matter - so:

data have;
  a="123456";
run;                                                                                

proc sql;
  create table WANT as
  select  A,
          cats(repeat("0",20-lengthn(A)),A) as B,
          put(input(A,best.),z20.) as C
  from    HAVE;
quit;

Re: How do I pad a character variable with leading zeroes using PROC SQL

MattSan — Tue, 25 Oct 2016 12:22:12 GMT

Thank you for your response. I am still fairly new to SAS and therefore I am not certain of the processing overhead of some data steps vs PROC SQL however I have been told by my colleagues that using data step "merge" vs a SQL "join" procedure is much slower. I would like to mark your answer as the solution however I am not sure what variables "B" and "C" refer to? Please could you perhaps clarify this?

Re: How do I pad a character variable with leading zeroes using PROC SQL

RW9 — Tue, 25 Oct 2016 12:41:41 GMT

Well, I say they "could" be right in certain circumstances, but its very dependant on scenario and a wide ranging statement of SQL > datastep at merging is misleading at best.

B and C refer to variables in the output dataset, it takes the form of

If you run the code in SAS you will see the output.

Re: How do I pad a character variable with leading zeroes using PROC SQL

Kurt_Bremser — Tue, 25 Oct 2016 12:55:01 GMT

@MattSan wrote:

Thank you however I was looking for a PROC SQL solution if possible? I need a PROC SQL version in order to possibly increase the processing speed on a large dataset.

PROC SQL will not speed up the processing; unless your dataset resides in a remote RDBMS that has a lot more computing power than your SAS host, where SQL pass-through will have a noticeable effect.

In pure SAS, SQL is at best just as fast as a data step merge. And often considerably (up to several orders of magnitude) slower.

Re: How do I pad a character variable with leading zeroes using PROC SQL

MattSan — Fri, 28 Oct 2016 06:44:54 GMT

My dataset does reside in a remote RDBMS and the extra effort to sort the data before merging the datasets is unwarranted.