topic Re: how to specify informat for numeric values with $ sign in SAS Programming

how to specify informat for numeric values with $ sign

ariyurjana — Tue, 28 Aug 2018 05:20:05 GMT

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

Re: how to specify informat for numeric values with $ sign

Kurt_Bremser — Tue, 28 Aug 2018 05:23:36 GMT

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

Re: how to specify informat for numeric values with $ sign

novinosrin — Tue, 28 Aug 2018 05:24:00 GMT

$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

Re: how to specify informat for numeric values with $ sign

ariyurjana — Tue, 28 Aug 2018 06:09:25 GMT

It specifies that the values are in billions

regards

jana

Re: how to specify informat for numeric values with $ sign

ariyurjana — Tue, 28 Aug 2018 06:13:13 GMT

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

Re: how to specify informat for numeric values with $ sign

Kurt_Bremser — Tue, 28 Aug 2018 06:16:41 GMT

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)

Re: how to specify informat for numeric values with $ sign

ariyurjana — Tue, 28 Aug 2018 06:19:39 GMT

hi,

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

regards

jana

Re: how to specify informat for numeric values with $ sign

Kurt_Bremser — Tue, 28 Aug 2018 06:41:44 GMT

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.

Re: how to specify informat for numeric values with $ sign

ariyurjana — Tue, 28 Aug 2018 07:03:02 GMT

Wonderful worked like a charm without any modifications.

thanks for your help.