CONVERTING NUMERIC TO CHARACTER

[Banke]

I have a simple excel file that I have to convert to CSV to import to my SAS server. Every attempt to make SAS read the variable titled "NDC" as a character variable

DATA MYHUMANA.NDC2;
SET MYHUMANA.NDC;
NDC = INPUT(NDC, BEST12.));
RUN;

DATA MYHUMANA.NDC2;
SET MYHUMANA.NDC;
NDC = STRIP(PUT(NDC, BEST12.));
RUN;

DATA MYHUMANA.NDC2;
SET MYHUMANA.NDC;
NDC_new = STRIP(PUT(NDC, BEST32.));
RUN;

has been futile. I converted to text in the original excel file and it did not work, I also tried many SAS codes to convert to a character variable but it did not work. I don't seem to understand where I am wrong, I am so confused. I have out some of the codes I used and the excel file, please help. Thanks

[Kurt_Bremser]

I opened your Excel file with Excel, saved it as csv, uploaded the csv file to my On Demand account, and ran this:

data ndc_code;
infile "~/ndc code.csv" dlm=";" dsd truncover firstobs=2;
length
  dx 8
  ndc $11
;
input
  dx
  ndc
;
run;

which gave me a numeric and character column as wanted.

NEVER use PROC IMPORT to read a csv (or any other text) file, always write the data step yourself. The time you think you save is outweighed by the time you have to spend to fix all the messes caused by PROC IMPORT's guessing.

But even if you directly open the xlsx file as a library by doing

libname xlin xlsx '~/ndc code.xlsx';

, the dataset SHEET1 has the ndc column as character; but you get an empty column "C" because the third column in the Excel file must have been used once upon a time.

[Banke]

LOG

17   data WORK.ndc_code;
18   /*infile "~/ndc code.csv" dlm=";" dsd truncover firstobs=2;*/
19   infile "C:\Users\HP SPECTRE\Desktop\Humana\ndc.csv" dlm=";" dsd truncover firstobs=2;
20
21   length
22     dx 8
23     ndc $11
24   ;
25   input
26     dx
27     ndc
28   ;
29   run;

NOTE: The infile "C:\Users\HP SPECTRE\Desktop\Humana\ndc.csv" is:
      Filename=C:\Users\HP SPECTRE\Desktop\Humana\ndc.csv,
      RECFM=V,LRECL=32767,File Size (bytes)=18889,
      Last Modified=07Sep2022:07:00:45,
      Create Time=07Sep2022:07:44:12

NOTE: Invalid data for dx in line 2 1-14.
RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
2         1,00054014225, 14
dx=. ndc=  _ERROR_=1 _N_=1
NOTE: Invalid data for dx in line 3 1-14.
3         1,00115115201, 14
dx=. ndc=  _ERROR_=1 _N_=2
NOTE: Invalid data for dx in line 4 1-14.
RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
4         1,00115115202, 14
dx=. ndc=  _ERROR_=1 _N_=3
NOTE: Invalid data for dx in line 5 1-14.
5         1,00378282277, 14
dx=. ndc=  _ERROR_=1 _N_=4
NOTE: Invalid data for dx in line 6 1-14.
6         1,16252052501, 14
dx=. ndc=  _ERROR_=1 _N_=5
NOTE: Invalid data for dx in line 7 1-14.
7         1,23155014901, 14
dx=. ndc=  _ERROR_=1 _N_=6
NOTE: Invalid data for dx in line 8 1-14.
8         1,42291013290, 14
dx=. ndc=  _ERROR_=1 _N_=7
NOTE: Invalid data for dx in line 9 1-14.
9         1,47781034201, 14
dx=. ndc=  _ERROR_=1 _N_=8
NOTE: Invalid data for dx in line 10 1-14.
10        1,50419086251, 14
dx=. ndc=  _ERROR_=1 _N_=9
NOTE: Invalid data for dx in line 11 1-14.
11        1,51862021201, 14
dx=. ndc=  _ERROR_=1 _N_=10
NOTE: Invalid data for dx in line 12 1-14.
12        1,64380076006, 14
dx=. ndc=  _ERROR_=1 _N_=11
NOTE: Invalid data for dx in line 13 1-14.
13        1,69543012210, 14
dx=. ndc=  _ERROR_=1 _N_=12
NOTE: Invalid data for dx in line 14 1-14.
14        1,69543012211, 14
dx=. ndc=  _ERROR_=1 _N_=13
NOTE: Invalid data for dx in line 15 1-14.
15        1,76439012210, 14
dx=. ndc=  _ERROR_=1 _N_=14
NOTE: Invalid data for dx in line 16 1-14.
16        1,76439012211, 14
dx=. ndc=  _ERROR_=1 _N_=15
NOTE: Invalid data for dx in line 17 1-14.
17        1,00054014025, 14
dx=. ndc=  _ERROR_=1 _N_=16
NOTE: Invalid data for dx in line 18 1-14.
18        1,00115115001, 14
dx=. ndc=  _ERROR_=1 _N_=17
NOTE: Invalid data for dx in line 19 1-14.
19        1,00115115002, 14
dx=. ndc=  _ERROR_=1 _N_=18
NOTE: Invalid data for dx in line 20 1-14.
20        1,00378282077, 14
dx=. ndc=  _ERROR_=1 _N_=19
NOTE: Invalid data for dx in line 21 1-14.
WARNING: Limit set by ERRORS= option reached.  Further errors of this type will not be printed.
21        1,16252052301, 14
dx=. ndc=  _ERROR_=1 _N_=20
NOTE: 1180 records were read from the infile "C:\Users\HP SPECTRE\Desktop\Humana\ndc.csv".
      The minimum record length was 14.
      The maximum record length was 14.
NOTE: The data set WORK.NDC_CODE has 1180 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

data ndc_code;
/*infile "~/ndc code.csv" dlm=";" dsd truncover firstobs=2;*/
infile "C:\Users\HP SPECTRE\Desktop\Humana\ndc.csv" dlm=";" dsd truncover firstobs=2;

length
  dx 8
  ndc $11
;
input
  dx
  ndc
;
run;

Thank you so much. I tried your code, please see the errors it gave me, where did  go wrong please? 

[Kurt_Bremser]

When you "Save as" to csv from Excel, it will create a semicolon-separated file. What you have is a real csv, so you need to use DLM="," in the INFILE statement.

[Tom · Accepted solution]

You cannot change the TYPE of an existing variable.  So this cannot work:

NDC = INPUT(NDC, BEST12.));

If NDC is character then the number created by the INPUT() function will be translated back into a character string to be assigned to the character variable NDC.

If NDC is already numeric then you don't need to statement. All it will do is truncate the value to match the precision that the BEST12 format will use to represent the value.

Also BEST is the name of a FORMAT, not an INFORMAT.

To convert a number to a character string you need to use the PUT() function and not the INPUT() function.  NDC codes do not have decimal places so there is no need to use the BEST format to figure out how many decimal places the value requires.

ndc_code = put(ndc,11.);

Also what type of NDC number do you have?  Is it a 10 digit code or the 11 digit code?  If the latter the placement of the hyphens is important.  https://health.maryland.gov/phpa/OIDEOR/IMMUN/Shared%20Documents/Handout%203%20-%20NDC%20conversion%...

[Banke]

Thank you, I had initially tried the "put" function, then I started using input as "trial and error"..😖. This code worked on my SAS windows but I am working on the project on an official SAS server. I don't know why the server keeps giving me errors. I have converted on my regular SAS and copied it to the server