how to specify informat for numeric values with $ sign

[ariyurjana]

hi,

I have data like this from a file 

 1 abcdefgh bil                  nigeria     $999.5B   $99.1B   $789.1B   $123.4B  $235.2B  $9089.1B.

I am using data step to read this data , below is my code

data dsbig ;
infile 'c:\big.dat' ;
input rank :3. cmpname :&$26. country :&$15. sales :dollar5.1 +1 profit :dollar5.1 +1 asst :dollar5.1 +1 mktval :dollar5.1 +1 fldval1 :dollar5.1 +1 fldval2 :dollar5.1 +1 ;
run;

I am not able to read the numeric data with dollar signs at all whereas the character data is being read properly .

I have tried options like dollar.  ,  comma5.1 , comma., but nothing seems to work.

The question is how to specify informat for numeric data that have $ signs attached to it ?

regards

jana

[Kurt_Bremser]

Your problem is not the dollar sign, but the "B". What does it mean?

[ariyurjana]

It specifies that the values are in billions

regards

jana

[Kurt_Bremser]

If all those values (all columns, all rows) will contain a B, you can use the compress function to get rid of them:

- read into a character variable

- remove the B with compress()

- read into numeric with the input() function

- drop the character variable

It might make sense to pack all of these actions into a macro, so you have to write and maintain the code only once. But first you have to get it to work for one of the columns.

If other characters might also appear, you have to account for those (set a multiplier conditionally)

[ariyurjana]

hi,

Will the idea of reading up to the last decimal point and using a +1 to skip the "B" not work.

regards

jana

[Kurt_Bremser · Accepted solution]

No. The +1 moves the column pointer before an item is read, it can't be reliably used to split column text. Note that you have columns of different lengths (the last one is too big for the $5.1 informat).

See this example:

%macro read_item(varname);
input _&varname. :$10. @;
&varname. = input(compress(_&varname.,'B'),dollar10.);
format &varname. dollar10.1;
drop _&varname.;
%mend;

data dsbig;
input rank :3. cmpname :&$26. country :&$15. @;
%read_item(sales);
%read_item(profit);
%read_item(asst);
%read_item(mktval);
%read_item(fldval1);
%read_item(fldval2);
cards;
1 abcdefgh bil                  nigeria     $999.5B   $99.1B   $789.1B   $123.4B  $235.2B  $9089.1B
;
run;

Note that you must not use decimals in informats unless you need to force the number of decimals for numbers that don't contain a decimal point. The dollar10. informat will find the decimal point on its own and act accordingly. dollar10.1, OTOH, would convert an input value of $9999 to 999.9, which is not what you want.

[ariyurjana]

Wonderful worked like a charm without any modifications.

thanks for your help.

[novinosrin]

$999.5B informat will fail bcoz of B in value. Do you wanna keep B in value?

if not read as char and strip the char part and convert to numeric

[ariyurjana]

hi,

No I dont want the "B". to skip that "B" I am using +1 in the input statement.

$433.5B for this 

for $433.5 the format is dollar5.1

for B i am using +1 to skip that value.

regards

jana