Convert char to num w leading zeroes

[Pandu]

Hi All,

I have two source tables and as per the requirement i need to convert char to num and keep the values as 01 insted of 1.

for that i have written below query and executed in Enterprise guide and its running successfully.

The problem is whan i executing same job in IDPortal it is getting error:

please take alook on below query and error:

PROC SQL;
 CREATE TABLE st.Final (DROP= _NAME_ ACTUAL LOWER UPPER ERROR STD _RECONSTATUS_ prebfovr
lowbfovr uppbfovr stdbfovr sProject) as select * from stg.final_CREAM as a left join in.GROUP_LOAD as b on
a.sBrand=b.sBrand and a.sFSGroup =b.sFSGroup and scan(a.CL4_NAME,1,'_')=b.CL4_NAME and a.Dist_Channel =INPUT(b.DISTR_CHAN,Z2.);

ERROR: The informat Z was not found or could not be loaded.

looking forward for your help and support.

[Tom]

The error message is pretty clear. 

The is no INFORMAT named Z. 

There is a FORMAT named Z. 

To convert a character string that looks like a normal number into an actual number just use the normal numeric informat.

CREATE TABLE st.Final 
  (DROP= _NAME_ ACTUAL LOWER UPPER ERROR STD _RECONSTATUS_ prebfovr
         lowbfovr uppbfovr stdbfovr sProject) as 
select * 
from stg.final_CREAM a 
left join in.GROUP_LOAD b
   on a.sBrand=b.sBrand 
  and a.sFSGroup =b.sFSGroup 
  and scan(a.CL4_NAME,1,'_')=b.CL4_NAME 
  and a.Dist_Channel =INPUT(b.DISTR_CHAN,32.)
;

The INPUT() function does not care if the width used on the informat specification used is larger than the length of the string being read so just use 32. as the INFORMAT since 32 is the maximum width that informat supports.

INFORMATs convert text to values. You use them with INPUT statement or the INPUT() function.

FORMATs convert values to text. You use them with PUT statement or the PUT() function. 

[Pandu]

Thank you  and i will change the query as you suggest.
will it convert the values 1 as 01 ?
(2 as 02)

(3 as 03) 

[Tom]

---

@Pandu wrote:

Thank you  and i will change the query as you suggest.
will it convert the values 1 as 01 ?
(2 as 02)

(3 as 03) 

---

That seems backwards.  Your code is using the INPUT() function with a numeric informat.  So it will be converting strings into numbers.  So it will convert things like '1' or '01' into the number 1. And strings like '2' or '02' into the number 2.

If you display the numbers 1 and 2 using the Z2. format then then will print as the string '01' and '02', but they are still have the actual numbers 1 and 2 in them.  FORMATS just impact how the values are displayed.

[Tom]

As a string '1' and '01' are two diferent things.  But once you convert them into actual numbers they are the same thing.

So are all of this other ways of displaying 1 as a string.

001
1.0
01.00000

If you want SAS to print the number 1 as the string '01' then attach the Z2. format to the variable.

[Pandu]

yes, Thank you but i have already declared for the variable as 

attrib Dist_Channel length = 8
format = z2.
label = 'DISTR_CHAN';

[Tom]

There are two different variables in your code, they have similar names, but they are coming form different datasets.

Your code is attempting to convert the one named  DISTR_CHAN into numbers so you can compare it to the one named Dist_Channel.