How Do Use OTHER in numeric field with CNTLIN

[BrentonCSmith]

I am importing an excel file.  The start and end is numeric.  When I try to import the character "OTHER" SAS can't digest it.

proc import datafile='/folders/myfolders/Informat Test.csv' out=Formats
 replace dbms=csv; run;
 
Proc format Cntlin=formats;
Run;

I get an import unsuccessful :

NOTE: The infile '/folders/myfolders/Informat Test.csv' is:

Filename=/folders/myfolders/Informat Test.csv,

Owner Name=root,Group Name=vboxsf,

Access Permission=-rwxrwx---,

Last Modified=15Aug2018:11:04:31,

File Size (bytes)=1583

NOTE: Invalid data for Start in line 55 18-22.

NOTE: Invalid data for End in line 55 24-28.

NOTE: Invalid data for Label in line 55 30-31.

RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0

55 f1956DiscountTbl,OTHER,OTHER,NA 31

FMTName=f1956DiscountTbl Start=. End=. Label=. _ERROR_=1 _N_=54

NOTE: 54 records were read from the infile '/folders/myfolders/Informat Test.csv'.

The minimum record length was 25.

The maximum record length was 31.

NOTE: The data set WORK.FORMATS has 54 observations and 4 variables.

NOTE: DATA statement used (Total process time):

real time 0.02 seconds

cpu time 0.01 seconds

Errors detected in submitted DATA step. Examine log.

54 rows created in WORK.FORMATS from /folders/myfolders/Informat Test.csv.

ERROR: Import unsuccessful. See SAS Log for details.

I am trying to approximate :

Proc format;

value fmt

1 = "70"

2 = "80"

other = "NA";

Run;

[ballardw]

As a learning exercise I might suggest creating an CNTLOUT data set from your example format and examining the result.

Proc format library=work cntlout=work.fmts;
value fmt
1 = "70"
2 = "80"
other = "NA"
;
Run;

While the start and label value appear in the format the real key is the HLO variable.

[BrentonCSmith]

I did that without much success.  I think I need to define start and end as a $ value, and add the other later.  It is really just a nice to have that is creating a not nice headache.

[ballardw · Accepted solution]

Without actual data from the file you are reading it is hard to tell what exactly may be needed.

You might be able to paste the csv into code box opened using the forum's {I} icon.

As a minimum on the rows of your input data with the "other" value you need to have the HLO variable with the value "O".

It isn't that hard. Something like:

data need;
   set formats;
   if start='OTHER' then do;
      start='**OTHER**';
      end  ='**OTHER**';
      HLO='O';
   end;
run;

And use that for the cntlin data set. If you have other types of formats such as multilabel then you'll need to make HLO a wider variable to hold more information. You may also want a TYPE variable to indicate N for numeric of C for character.

Or if practical go back to whover supplied that CSV and see if they could make a cntlout data set for you (possibly in a transport data file for cross system use).

[BrentonCSmith]

The "problem" was in the Proc Import.  The start and end values were read in as numeric.  I ended up adding a 2nd row to the excel table with X's in each variable.  It works but looks klugey as heck.  But your direction on the HLO variable was what moved me forward...  Thanks,

proc import datafile='/folders/myfolders/Informat Test.csv' out=Formats
     replace dbms=csv;
 run;


Data ConvertToChar;
     Length label $ 32;
     
     set Formats(where=(start NOT EQ "x")) end=EOF;
     
     Type = "N";
     HLO  = " ";
          
     output;
     IF EOF then do;
        Start = "**OTHER**";
        End   = "**OTHER**";
        LABEL = "N/A";
        HLO = "O";
        Output;
     end;

 run;   



Proc format Cntlin=ConvertToChar;
Run;

[BrentonCSmith]

Thanks, The HLO is the key variable.

I tried that earlier. Part of the problem was that the import process set the start and end values to numeric.  I had to add a 2nd record to the excel file that would get the start and end variables to $.  That explains the NOT EQ coding.  It looks kind of Klugey.

proc import datafile='/folders/myfolders/Informat Test.csv' out=Formats
     replace dbms=csv;
 run;


Data ConvertToChar;
     Length label $ 32;
     
     set Formats(where=(start NOT EQ "x")) end=EOF;
     
     Type = "N";
     HLO  = " ";
          
     output;
     IF EOF then do;
        Start = "**OTHER**";
        End   = "**OTHER**";
        LABEL = "N/A";
        HLO = "O";
        Output;
     end;

 run;   


Proc format Cntlin=ConvertToChar;
Run;