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 –
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 |
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);
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);
Like this?
NUM_VALUE = input(ITEM_VALUE, ?? 32.);
if NUM_VALUE then ITEM_VALUE=put(NUM_VALUE, comma32.0 -l);
?? avoids error messages if the value cannot be read with the informat.
-l left-justifies the formatted value
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.