topic Re: Convert num into value with less decimals in SAS Programming

Convert num into value with less decimals

Konkordanz — Thu, 25 Nov 2021 12:19:35 GMT

Hallo forum,

I want to convert a numeric value with dots into a value with comma and only six decimals (num or char). All missing cells shall get a minus ("-").

v1_have	v1_want
137.10254134	137,102541
11.761354987	11,761354
.	-
.	-
.	-
11.761354987	11,761354
125.25814946	125,258149
0.083036885	0,083036

I thought, its pretty easy. But I just cant get it to work. I tried it for example with:

data want;
set have;
format v1_want commax20.6;
v1_want=substr(v1_have,1,find(v1_have,".")+6);
if missing(v1_have) then v1_want="-";
run;

Can you help me? Thank you!

Re: Convert num into value with less decimals

PaigeMiller — Thu, 25 Nov 2021 12:44:00 GMT

Is your question that you want to change the appearance of the data, while leaving the underlying data with more than six decimal places unchanged?

Or is your question how to round the actual numbers to 6 decimal places?

These two are not the same and should not be confused with one another.

What is the reason you want to do this? To show the numbers in a report? To do additional calculations with the data? Something else?

Re: Convert num into value with less decimals

Tom — Thu, 25 Nov 2021 17:54:56 GMT

The SUBSTR() function requires CHARACTER string input. You cannot use it with a number without first converting the number into a string. If you don't make the conversion then SAS will and it will use the BEST12. format when making the conversion.

If you just want to DISPLAY the number with a fixed number of decimal places then use a FORMAT. Either attach the format to the new variable in the new dataset. Or use a FORMAT statement in the procedure that you are using that is going to display the number.

data want;
  set have;
  v1_want=v1_have;
  format v1_want commax20.6;
run;

If you want to reduce the precision of the number then use something like ROUND().

  v1_want=round(v1_have,0.000001);

If you want the missing value to display as - instead of . then you could change the MISSING option. But that would apply to ALL missing values, not just those in V1_WANT.

If you want you could create a custom format to display V1_WANT as '-' then missing and using COMMAX format otherwise.

proc format ;
  value myfmt .='-' other=[commax20.6];
run;

Or you could make V1_WANT be a character string instead of a number.

data want;
  set have;
  length v1_want $20 ;
  if v1_have=. then v1_want='-';
  else v1_want=put(v1_have,commax20.6);
run;

Re: Convert num into value with less decimals

Konkordanz — Mon, 29 Nov 2021 10:58:33 GMT

Thank you for your help!

@PaigeMiller What is the reason you want to do this? To show the numbers in a report? To do additional calculations with the data? Something else?

Yeah, i want to built a table for someone else, they work with it and calculate with the data in excel.

@Tom

The proc-format-step works within SAS. But:

This format doesnt retain when I export my table into a excel-file. I tried several ways. Proc-print and proc-Report - it needs a lot of time because of my huge dataset (~44.000*13 cells); seems not practical. Furthermore Excel shows an error when I download the excel-file with SAS.

Is there a better way to get the formatted table into an excel-sheet?

Re: Convert num into value with less decimals

PaigeMiller — Mon, 29 Nov 2021 11:07:30 GMT

If "someone else" is going to work with the data in Excel, you don't need formatting at all. Just have SAS send them the unformatted numbers and let them do calculation and format the results to their liking.

Also, rounding the data and then giving the results to someone else to do additional calculations is not a good idea. Rounding causes loss of precision! The rule is to round only as the last step; you never round as an intermediate step.

Re: Convert num into value with less decimals

Konkordanz — Mon, 29 Nov 2021 11:08:56 GMT

PaigeMiller, actually right. But I have to check, if my created table matches the patterntable. The patterntable has the format, I asked for.

And: I want to learn SAS and just want to find a solution for my Problem ;)...

Re: Convert num into value with less decimals

Konkordanz — Mon, 29 Nov 2021 11:32:45 GMT

So, my question is:

I formatted my dataset with the proc-format-command. Afterwards I created the final table with an proc-sql-command. How can I export this into a excel-sheet with the same formats?