Import data step returns missing values

[mariapf]

Hi all,

I am trying to correctly import a csv file with numeric data containing pretty long numbers - usually about 16 digits long with 10 decimals. Accuracy is important because I need to use these variables to make calculations.

When using proc import, it automatically imports these numeric variables with a best12. format, thus truncating the values down to 6 decimals and causing me precision problems in my calculations (it really adds up). Looking at posts in the community I have found some advice recommending to import using a data step in order to specify my desired variable formats.

I am trying to run the code you can find attached below. The funny thing is that this code doesn't seem to correctly read variables annuit_e onward. I know for a fact the values are not missing since proc import has no problem reading the data.

If you have any idea of why this code is giving me problems I would greatly appreciate it.

Thank you for your time

DATA WORK.RAFM_022018    ;
             %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
             infile '/opt/sas/config/Lev1/SASApp/Data/IFRS17_SSR/INPUTS/022018/PROYECCIONES/RAFM_022018.csv' 
                        DELIMITER = ';'
       			MISSOVER 
       			DSD 
       			LRECL=32767 
       			FIRSTOBS=2 ;
       		
               informat VIVA $1. ;
               informat "NP / Cartera"N $2. ;
               informat "Group ID"N $100. ;
               informat PORTFOLIO $8. ;
               informat "Tasa Actual"N best32. ;
               informat LiR best32. ;
               informat "Pago esperado NDIC"N best32. ;
               informat "NDIC esperado"N $1. ;
               informat "CSM tecnico"N $1. ;
               informat "CSM Financiero"N $1. ;
               informat "UoC (BEL prestaciones)"N $1. ;
               informat "UoC (Valor actual)"N best32. ;
               informat "Intereses CSM Financiero"N $1. ;
               informat "Amort CSM financiero"N $1. ;
               informat "Acred NDIC a LiR"N $1. ;
               informat Participacion $1. ;
               informat '% Cobert RA'N best32. ;
               informat "Mes efecto"N best32. ;
               informat END_COV_MONTH best32. ;
               informat "Year efecto"N best32. ;
               informat END_COV_YEAR best32. ;
               informat annuit_e best32. ;
               informat cal_month best32. ;
               informat cal_year best32. ;
               informat clm_misc best32. ;
               informat death_clai best32. ;
               informat exp_acq best32. ;
               informat exp_adm best32. ;
               informat maturities best32. ;
               informat premium_b best32. ;
               informat res_b best32. ;
               informat srr_claim best32. ;
             
               format VIVA $1. ;
               format "NP / Cartera"N $2. ;
               format "Group ID"N $100. ;
               format PORTFOLIO $8. ;
               format "Tasa Actual"N best32. ;
               format LiR best32. ;
               format "Pago esperado NDIC"N best32. ;
               format "NDIC esperado"N $1. ;
               format "CSM tecnico"N $1. ;
               format "CSM Financiero"N $1. ;
               format "UoC (BEL prestaciones)"N $1. ;
               format "UoC (Valor actual)"N best32. ;
               format "Intereses CSM Financiero"N $1. ;
               format "Amort CSM financiero"N $1. ;
               format "Acred NDIC a LiR"N $1. ;
               format Participacion $1. ;
               format '% Cobert RA'N best32. ;
               format "Mes efecto"N best32. ;
               format END_COV_MONTH best32. ;
               format "Year efecto"N best32. ;
               format END_COV_YEAR best32. ;
               format annuit_e best32. ;
               format cal_month best32. ;
               format cal_year best32. ;
               format clm_misc best32. ;
               format death_clai best32. ;
               format exp_acq best32. ;
               format exp_adm best32. ;
               format maturities best32. ;
               format premium_b best32. ;
               format res_b best32. ;
               format srr_claim best32. ;
            input
                        VIVA  $
                        "NP / Cartera"N  $
                        "Group ID"N  $
                        PORTFOLIO  $
                         "Tasa Actual"N
                        LiR
                        "Pago esperado NDIC"N
                        "NDIC esperado"N  $
                        "CSM tecnico"N  $
                        "CSM Financiero"N  $
                        "UoC (BEL prestaciones)"N  $
                        "UoC (Valor actual)"N
                        "Intereses CSM Financiero"N  $
                        "Amort CSM financiero"N  $
                        "Acred NDIC a LiR"N  $
                        Participacion  $
                        '% Cobert RA'N
                        "Mes efecto"N
                        END_COV_MONTH
                        "Year efecto"N
                        END_COV_YEAR
                        annuit_e
                        cal_month
                        cal_year
                        clm_misc
                        death_clai
                        exp_acq
                        exp_adm
                        maturities
                        premium_b
                        res_b
                        srr_claim
           ;
            if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
            run;

[ballardw]

First thing, save yourself a lot of future grief by changing all the name literals to regular SAS variable names.

If you want nicer text to display assign LABELs to the variables.

Example: replace "NP / Cartera"N with maybe NP_Cartera.

What does the LOG look like when you run the data step? Run code and then go the log and copy the code plus any notes or warnings. Then paste into a text box on the forum opened with the </> icon.

Best would be to paste a few rows of the data copied from the CSV with a text editor like Notepad into a separate text box but that's a lot of variables to clean up if you don't want to show such details.

Did you ever save this CSV after opening with a spreadsheet program? Several of these like Excel will change values assuming it knows better what the data should be than you do. So you may now have values that are somewhat corrupted from what you expect.

One thing that can cause problems similar to what you describe would be a single quote in an unexpected place but that would typically cause one or more "invalid data" messages or only affect one source row at a time.

The FORMAT would have no affect on calculations, it just means fewer digits may be displayed which might make you think the result is incorrect. Run this code and look at the log. the value for x is 0.125 but displays differently depending on the format.

data example;
   x = 1/8;
   put "best2 format" x= best2. ;
   put "best4 format" x= best4. ;
   put "best5 format" x= best5. ;
   y = x*8;
   put y= best2. y= best4. y= best6.;
run;

With 10 decimals it is quite possible that calculations may exceed the precision that SAS variables are stored with though.