topic Re: importing data listed in billions of dollars in SAS Enterprise Guide

importing data listed in billions of dollars

Damon1 — Wed, 19 Feb 2020 00:53:24 GMT

Hi Everyone,

Been learning for about a week. So, i have a data set that follows the following pattern:

rank (numeric), company ($), country ($), sales (num), profits (num), assets (num), market value (num)

the data under the last 4 numeric variables are presented in billions of dollars, as "$1.5B" or "$200.1B", and vary in length

Im using the following code:

DATA bigcos;
INFILE 'C:\Users\dlobsien\Documents\bigcompanies.txt';
INPUT ranking company_name $26. +1 country $18. sales_in_billions :dollar10. profits_in_billions :dollar10. assets_in_billions :dollar10. +1 profits_in_billions :dollar10.;
title companies data;
RUN;

——————————-

PROC PRINT data=bigcos (obs=15);
Run;

the first three variables go in fine, but it wont read anything after that with the Bs in the way (i assume that's the issue). whenever i add a line that says "compress(var, 'B')", after the input lines it gives me 0 observations—maybe I'm entering the syntax wrong or putting it in the wrong spot.

I've also included the data set in case that is useful.

Any help would be greatly appreciated.

Re: importing data listed in billions of dollars

Tom — Wed, 19 Feb 2020 01:20:02 GMT

You mentioned that the values vary in length, but it does not look that way when I viewed your attached text file using this websites viewer tool. The values of a given field are always in the same location on the line.

Why not just skip that column in your INPUT statement. Looks like you already know how to skip a column since you have included the +1 cursor movement command in your current INPUT statement.

If the value is not always a B then read it as another character variable and then you can write a program to adjust the magnitude of the number read based on what letter was appended.

Hint: You don't want to use the : modifier on your informats. That will switch your input statement from formatted mode to list mode.

Re: importing data listed in billions of dollars

Damon1 — Wed, 19 Feb 2020 01:23:12 GMT

yeah, I'm not actually sure how to use the +1. i added on a whim it because i noticed it in a textbook, and added it one at a time because I think made the character variables read.

the data under the last 4 numeric variables vary from as short as $4.9B to as long as $2,265.8B, and each observation for those variables ends in "B"

Re: importing data listed in billions of dollars

Reeza — Wed, 19 Feb 2020 02:34:34 GMT

https://communities.sas.com/t5/SAS-Programming/How-do-I-properly-use-PROC-IMPORT-for-a-multi-spaced-delimited/m-p/625656#M184421

Classmates?

You need to write your own informat preferably but you could also just read it as a character, remove the B and then convert it to billions.

https://www.lexjansen.com/pharmasug/2005/posters/po06.pdf

Re: importing data listed in billions of dollars

Damon1 — Wed, 19 Feb 2020 02:44:16 GMT

probably—its an online class so i couldnt tell you lmao.

i imported it as a character, then compressed out the $ and B then made a new data set and wrote varname=import(oldvar, best.)

and that worked well enough

Re: importing data listed in billions of dollars

Tom — Wed, 19 Feb 2020 03:26:00 GMT

Include the leading spaces when counting how many characters the field takes. So if the field before the one with B in it ends in column 20 and the B is in column 30 then use COMMA9. informat to read the 9 characters in columns 21 to 29.
... @21 number comma9. letter $1. ...