BookmarkSubscribeRSS Feed
Pandu
Fluorite | Level 6

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.

6 REPLIES 6
Tom
Super User Tom
Super User

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
Fluorite | Level 6

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
Super User Tom
Super User

@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
Super User Tom
Super User

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
Fluorite | Level 6

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

 

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

Tom
Super User Tom
Super User

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.

 

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1406 views
  • 1 like
  • 2 in conversation