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:
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.
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.
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.
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.
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.