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
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.
Your problem is not the dollar sign, but the "B". What does it mean?
It specifies that the values are in billions
regards
jana
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)
hi,
Will the idea of reading up to the last decimal point and using a +1 to skip the "B" not work.
regards
jana
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.
Wonderful worked like a charm without any modifications.
thanks for your help.
$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
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: