topic Re: CONVERTING NUMERIC TO CHARACTER in New SAS User

CONVERTING NUMERIC TO CHARACTER

Banke — Wed, 07 Sep 2022 11:42:13 GMT

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

Re: CONVERTING NUMERIC TO CHARACTER

Kurt_Bremser — Wed, 07 Sep 2022 12:19:59 GMT

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.

Re: CONVERTING NUMERIC TO CHARACTER

Tom — Wed, 07 Sep 2022 12:41:10 GMT

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%20to%2011%20digits.pdf

Re: CONVERTING NUMERIC TO CHARACTER

Banke — Wed, 07 Sep 2022 13:40:50 GMT

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?

Re: CONVERTING NUMERIC TO CHARACTER

Banke — Wed, 07 Sep 2022 13:44:06 GMT

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

Re: CONVERTING NUMERIC TO CHARACTER

Kurt_Bremser — Wed, 07 Sep 2022 15:00:04 GMT

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.