topic Re: How to remove decimal for variable that is defined as character in the input statement in SAS Programming

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

LL5 — Thu, 16 Jul 2020 18:55:04 GMT

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 –

Is there a way to remove the decimal after the number?
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

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

Shmuel — Thu, 16 Jul 2020 20:11:53 GMT

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);

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

ChrisNZ — Fri, 17 Jul 2020 02:57:31 GMT

Like this?

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

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

ChrisNZ — Fri, 17 Jul 2020 03:06:56 GMT

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;

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

LL5 — Mon, 20 Jul 2020 20:57:30 GMT

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.

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

ChrisNZ — Mon, 20 Jul 2020 23:54:41 GMT

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

-l left-justifies the formatted value

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

LL5 — Tue, 21 Jul 2020 00:03:30 GMT

Thanks a lot!