BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LL5
Pyrite | Level 9 LL5
Pyrite | Level 9

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;

 

ItemNameInventoryNumberItem_IDItem_Value
SkinCareAA-372888ItemID11023.0
SkinCareAA-372888ItemID20.0
SkinCareAA-372888ItemID3Out of Stock
SkinCareAA-372888ItemID4N/A
SkinCareAA-372888ItemID5In Stock

 

1 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18

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

 

View solution in original post

6 REPLIES 6
Shmuel
Garnet | Level 18

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
Tourmaline | Level 20

Like this?

NUM_VALUE = input(ITEM_VALUE, ??  32.);
if NUM_VALUE then ITEM_VALUE=put(NUM_VALUE, comma32.0 -l);
LL5
Pyrite | Level 9 LL5
Pyrite | Level 9
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
Tourmaline | Level 20

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

-l left-justifies the formatted value

ChrisNZ
Tourmaline | Level 20

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;

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 2500 views
  • 4 likes
  • 3 in conversation