Comma after a number

[Caro17]

Hey,

I just want to add a comma after a lot of numbers. 

before

1234567

89012345

after

1234567,

89012345,

I have tried it with catx, but it does not function. Then I tried it with cats and on first sight it seems to function. But a closer look showed me that the comma ends as soon as the figure changes from seven-digit to eight-digit. From this point on there are no commas after the numbers.

And at the end I want to have the new column in an excel file. But the systems told me that the file cannot be opened or repaired as it is damaged.

data want

set order

order_no_str = put(lngorder_no, 8.);

format order_no_str$100.;

order_no_str=cats(order_no_str,’, ');

If length(order_no_str)>90 then do;

put 'ERROR: …';

end;

ods excel file '\\abc\agency\…\order2020.xlsx’;

run;

How can I solve these problems?

Thanx a lot!

Caro

[Hanno]

I am by no means an expert ..

Do you need your Variables(numbers) to be number or Character values ?

What would the use of the comma be for ?

Hanno

[Kurt_Bremser · Accepted solution]

With this

order_no_str = put(lngorder_no, 8.);
format order_no_str$100.;

you first define the length as 8. The later FORMAT statement will not change the length.

Do this instead:

length order_no_str$100.;
order_no_str = put(lngorder_no, 8.);

It is never a good idea to try to set the length implicitly through a FORMAT. Instead do it explicitly with LENGTH. If the LENGTH can't work for some reason, you'll get a note in the log; with FORMAT, you don't get that.

[Caro17]

Thank you for the quick help. But what about the other problem with the excel file?

[Kurt_Bremser]

ODS is the Output Delivery System. To have stuff in your Excel file, you need to create output, but your data step will "only" create a dataset.

And you need to close the ODS destination, or your Excel file will still be open for output.

data want;
set order;
length order_no_str $100;
order_no_str = cats(put(lngorder_no, 8.),',');
run;

ods excel file='\\abc\agency\…\order2020.xlsx’;

proc print data=want;
run;

ods excel close;

If you still have problems, post the complete log from your steps and ODS statements, using the </> button (required!).

[Caro17]

Do I really need proc print? It is not necessary to see it in SAS again.

Now I get an excel file, but without the comma after the number.

[Caro17]

And in excel I do not need the column heading.

[PeterClemmensen]

I you simply want to display a comma after a number, you can roll out a picture format like this

proc format;
   picture c other = '00000009,';
run;

data have;
   x = 1234567; output;
   x = 89012345; output;
   format x c.;
run;