BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
zdassu
Quartz | Level 8

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26
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

View solution in original post

7 REPLIES 7
PeterClemmensen
Tourmaline | Level 20

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.

zdassu
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26
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
zdassu
Quartz | Level 8

Thanks for your help, worked just as you described

ballardw
Super User

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

zdassu
Quartz | Level 8

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

ballardw
Super User

@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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1035 views
  • 0 likes
  • 4 in conversation