turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Doing a sum on a text field

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-01-2017 08:11 AM

Hi I am trying to sum a column in a table, how ever although the column contains numbers they have been saved as text. I know in SQL you can use the following code but I am struggling to use this code in SAS

select SUM(cast(AAAAAAAAAA as float))/1000000 from ##Temp1

Your help in this matter is much appreciated

Accepted Solutions

Solution

11-01-2017
08:26 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to zdassu

11-01-2017 08:19 AM

The equivalent of cast in SAS is input/put. Put takes a numeric and creates a character based on the format, input takes a character and returns a numeric based on the informat. Now these will give you a warning if there are items that cannot be converted, e.g. ABC cannot be numeric, these will be missing which is dot. So yu could do:
select sum(input(text_var,best.)) as result

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to zdassu

11-01-2017 08:14 AM

What does your data look like?

Sounds like you need to convert the character values to numeric first and then calculate the sum from there, eg with PROC MEANS.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to draycut

11-01-2017 08:17 AM

The data looks like this, quite a few decimal places

7333.83706005760236

-241.48477505034183

-3721.90979470953705

282.78293262436968

8401.59702862381179

319.66827473169031

9488.22997865952625

276.11825793635553

8379.37965250339576

1001.47034302957246

Solution

11-01-2017
08:26 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to zdassu

11-01-2017 08:19 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-01-2017 08:27 AM

Thanks for your help, worked just as you described

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to zdassu

11-01-2017 09:57 AM

With that many decimals you may run into numeric precision issues.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

11-01-2017 10:24 AM

DO you have any other suggestions? I don't mind it rounding the decimals up to about 6-7 places

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to zdassu

11-01-2017 06:54 PM

zdassu wrote:

DO you have any other suggestions? I don't mind it rounding the decimals up to about 6-7 places

I brought up the issue in case you considered all of those digits as significant. Your calculations involving such numbers could be off in the last one or two decimals (or more if enough calculations are done). You just need to know your data and treat as appropriate for your purpose.

I obviously don't have your data but if there are digits that are not actually significant that you carry through calculations then your result may imply precision that does not exist which has a chance of creating final statistics that are/are not significant based on using those values in a regression or similar procedure.