How to remove decimal for variable that is defined as character in the input statement

[LL5]

I use data step with inflie and input statement to read a csv file. There is a column (Item_Value) that mixes with character and numeric value.

I use informat $50. to define this column. However, when the SAS dataset is generated, I am seeing a decimal after the number (see code and output below).

I have two questions –

1. Is there a way to remove the decimal after the number?
2. Is there a way to include comma for thousand?

Thanks.

data test1;

  infile "/workfolder/warehouse/data/monthly/June.csv" dlm= ',' missover DSD lrecl=32767 firstobs=2 ;

  input

    ItemName                  : $50.     

    InventoryNumber                 : $50.

    Item_Id                        : $50.

    Item_Value                     : $50.

    ;

RUN;

ItemName	InventoryNumber	Item_ID	Item_Value
SkinCare	AA-372888	ItemID1	1023.0
SkinCare	AA-372888	ItemID2	0.0
SkinCare	AA-372888	ItemID3	Out of Stock
SkinCare	AA-372888	ItemID4	N/A
SkinCare	AA-372888	ItemID5	In Stock

[Shmuel · Accepted solution]

Dealing with column Item_value as given, and assuming the value is either numeric 

or a string (a comment) - I would create two new variables:

num_value = input(compress(item_value), ??  best10.);
if missing(num_value) then
 comment_value = compbl(item_value);

[ChrisNZ]

Like this?

NUM_VALUE = input(ITEM_VALUE, ??  32.);
if NUM_VALUE then ITEM_VALUE=put(NUM_VALUE, comma32.0 -l);

[LL5]

Hi ChrisNZ. Your approach works like a charm. Thanks a lot. I am trying to understand what does the ?? do in the 1st input statement, and what does the comma32.0 -l means in the 2nd put statement. It'd be great if you could share some knowledage with me.

[ChrisNZ]

?? avoids error messages if the value cannot be read with the informat.

-l left-justifies the formatted value

[LL5]

Thanks a lot!

[ChrisNZ]

You could even only keep the numeric variable.

proc format;
  value stock
  .S   ='In Stock'
  .O   ='Out of Stock'
  other=[comma32.];
run;

data TEST;
  infile ....;
  input .... ;
  if      ITEM_VALUE = 'In Stock'     then NUM_VALUE = .S;
  else if ITEM_VALUE = 'Out of Stock' then NUM_VALUE = .O;
  else                                     NUM_VALUE = input(ITEM_VALUE, ??  32.);
  format NUM_VALUE stock.;
run;