AT LEAST ONE W.D FORMAT WAS TOO SMALL work around?

[MrBobSir]

On SAS Mainframe Release 9.4 , using  PROC SQL NOPRINT; to create a view.
One of the columns being read is currently w.d of 14.6 and is being expanded to w.d of 18.6. While not an issue on DB2 table, in SAS we're getting the "AT LEAST ONE W.D FORMAT WAS TOO SMALL FOR THE NUMBER TO BE PRINTED". 
The production w.d (14.6 defined on table) value appears as 15730.42663
The test w.d (18.6 defined on table) value appears as 1.573042

Any way we can get the 18.6 out or is there a limit (that we haven't been able to find?)

Thanks
Bob

[Tom]

If you want to eliminate the message from SAS that it cannot display value you having using the format you have attached to the variable why not just use the BEST format instead?  Then SAS will  adjust so that it can display the values you actually have.

But if you have a variable in DB2 that is DECIMAL with "precision" of 18 then you cannot store those values exactly as a number in a SAS dataset.  SAS uses 64bit floating point values to store numbers and that only allows a "precision" of 15 decimal digits.  Once you have more more non-zero digits than that the final decimal digits cannot be stored exactly.   

Do the last few decimal places really matter to you?  If not then just let SAS store the numbers and use the BEST format to display them.  That way you can display values as  large as 10**14 and small as 10**-4 in the same number of characters (with a little loss of details).

If so you will need to ask SAS or DB2 to convert the 18  digits into a character string in SAS.  (probably will take at least 19 characters since you will need to have a decimal point. Might need more if the values can be negative.)

[ballardw]

It may help to show the LOG with the step throwing this message. Copy the text from the log with the code and all the notes or messages from that step. On the forum open a text box using the </> icon above the message window and paste the text.

The text box helps in case there are any diagnostics as the message windows on this forum reformat text pasted into them. The text box also helps separate discussion from code/results.

Then tell us which variable you are discussing. Neither of the values shown in your discussion would hit either limit though the shift in decimal places is questionable.

The solution to remove that message is to assign a different format when using the variable(s) to one with a larger W value. You can go up to 32 if needed.

[MrBobSir]

1           PROC SQL NOPRINT;                                                     
2              EXECUTE(SET CURRENT DEGREE = 'ANY') BY DB2;                        
DB2 NOTE:  The QUERYNO for the EXPLAIN is 7113174                                 
                                                                                  
3              CREATE VIEW WORK.PRICFI (CIC_ZN,CIC_MDL,CCC,FAMILY,NEW_CCY,        
4                              CIC_PREM,CIC_PREM_UNRND,GRP_CD,STRT_DT,STOP_DT,    
5                           TTM_ADD_PREM,PRT_MRK_UP) AS                           
6              SELECT * FROM CONNECTION TO DB2                                    
7               (SELECT CIC_ZN,CIC_MDL,CVRG_CD,BIC_FAM,NEW_CCY,CIC_PREM,          
8                 CIC_PREM_UNRND,GRP_CD,STRT_DT,STOP_DT,TTM_ADD_PREM, PRT_MRK_UP  
9               FROM Z1Q1.Z1AD001$.NEW_CUR_PRC                                    
10              WHERE CIC_ZN = 'APDZONE1'                                         
11                    AND NEW_GOVT = 'NONGOVERNMENT'                              
12                    AND CVG_TYP = 'POWERTRAIN + HYDRAULICS'                     
13                    AND NEW_MO IN ('36')                                        
14                    AND PRT_MRK_UP = '25'                                       
15                    AND NEW_HR = 1000                                           
16                    AND CIC_MDL= '302'                                          
17                    AND STRT_DT= '2024-01-01'                                   
 18              FOR READ ONLY WITH UR);                                                                                            
12                                                          THE SAS SYSTEM                              11:19 THURSDAY, JULY 11, 202
                                                                                                                                    
 NOTE: SQL VIEW WORK.PRICFI HAS BEEN DEFINED.                                                                                       
 19               QUIT;                                                                                                             
 NOTE: THE PROCEDURE SQL USED 0.01 CPU SECONDS.                                                                                     
                                                                                                                                    
                                                                                                                                    
 20              %SSCFLAT(MSASDS=WORK.PRICFI,MPREFIX=USER1.PG34.);                                                                
 ***** SSCFLAT VER 1.06                                                                                                             
 ***** COPYRIGHT (C) 1999 SYSTEMS SEMINAR CONSULTANTS                                                                               
 ***** 608 278-9964                                                                                                                 
 NOTE: CONDITIONAL DISPOSITION OF DELETE WILL BE IGNORED FOR FILE USER1.PG34.PRICFI.DAT, EXCEPT FOR ABENDS WHICH SAS CANNOT       
       RECOVER.                                                                                                                     
 NOTE: CONDITIONAL DISPOSITION OF DELETE WILL BE IGNORED FOR FILE USER1.PG34.PRICFI.DAT, EXCEPT FOR ABENDS WHICH SAS CANNOT       
       RECOVER.                                                                                                                     
 DB2 NOTE:  The QUERYNO for the EXPLAIN is 7113174                                                                                  
                                                                                                                                    
 NOTE: TABLE WORK.VCOLUMN CREATED, WITH 12 ROWS AND 5 COLUMNS.                                                                      
                                                                                                                                    
 NOTE: THE PROCEDURE SQL USED 0.01 CPU SECONDS.                                                                                     
                                                                          
                                                                          
                                                                          
 NOTE: THE FILE FLATOUT IS:                                               
       DSNAME=USER1.PG34.PRICFI.DAT,                                    
       UNIT=3390,VOLUME=AP0515,DISP=NEW,BLKSIZE=27998,                    
       LRECL=6160,RECFM=VB,CREATION=2024/07/11                            
                                                                          
 NOTE: 1 RECORD WAS WRITTEN TO THE FILE FLATOUT.                          
       THE MINIMUM RECORD LENGTH WAS 128.                                 
       THE MAXIMUM RECORD LENGTH WAS 128.                                 
 NOTE: THERE WERE 12 OBSERVATIONS READ FROM THE DATA SET WORK.VCOLUMN.    
 NOTE: THE DATA STATEMENT USED 0.01 CPU SECONDS.                          
                                                                          
                                                                          
 DB2 NOTE:  The QUERYNO for the EXPLAIN is 7113174                        
                                                                          
                                                                          
 NOTE: THE FILE FLATOUT IS:                                               
       DSNAME=USER1.PG34.PRICFI.DAT,                                    
       UNIT=3390,VOLUME=AP0515,DISP=MOD,BLKSIZE=27998,                    
       LRECL=6160,RECFM=VB,CREATION=2024/07/11                                                                               
                                                                                                                             
 NOTE: 0 RECORDS WERE WRITTEN TO THE FILE FLATOUT.                                                                           
 NOTE: THERE WERE 0 OBSERVATIONS READ FROM THE DATA SET WORK.PRICFI.                                                         
 NOTE: AT LEAST ONE W.D FORMAT WAS TOO SMALL FOR THE NUMBER TO BE PRINTED. THE DECIMAL MAY BE SHIFTED BY THE "BEST" FORMAT.  
 NOTE: THE DATA STATEMENT USED 0.01 CPU SECONDS.                                                                             
                                                                                                                             
                                                                                                                             

Field in question is CIC_PREM_UNRND. When the system shifts to "BEST" FORMAT it moves the decimal 4 places to the left. Is it possible to define the field in the select statement so we don't have the decimal moved 4 places? 
Thanks 

[Kurt_Bremser]

So it does not happen when fetching the data from the DB, but when the macro exports the data to a flat file.

Inspect the macro code. You will have to adapt the export code (a DATA step) to the new data format.

[Kurt_Bremser]

And, instead of creating a view, create a dataset in PROC SQL. Otherwise you will repeatedly run the same query in the DB, which is inefficient, to say the least.

[ballardw]

I would say your issue has very little to do with the SELECT or any SQL at all.

Your culprit is buried in the macro call

   %SSCFLAT(MSASDS=WORK.PRICFI,MPREFIX=USER1.PG34.);    

The output generated at one or more points in that macro is what is using the format(s) and would need to examine that.

You get more details of exactly which step or generated code is causing the message by setting Options Mprint; before the macro runs so the log will show the messages in relation to the code generated.

options mprint;

   %SSCFLAT(MSASDS=WORK.PRICFI,MPREFIX=USER1.PG34.);    

options noprint;

Normally when you get a change to Best format from a too small the NUMBER of decimal places changes, not the position. Too small generally means that the integer portion won't fit resulting in rounding of the decimal values or the value is enough incompatible with the format it shifts to scientific notation. Which might look like a shift of decimal point if the exponential part is ignored or lost for some reason.

data example;
   x=123456789;
run;

proc print data=example;
   format x 6.2;
run;

result is 1.23E8 because it is trying to fit into 6 character positions. 1.23 are the first 4 and E8 is the exponential part. It looks like decimal shift but only if you ignore the E8. From the text in your log about FILE FLATOUT it appears something is writing a text file and I have a sneaking suspicion that it may be going into a fixed column layout where possibly the exponent is getting overwritten in the output. Or perhaps someone isn't recognizing what the like E4 (or similar) means.