topic Re: Comma after a number in SAS Programming

Comma after a number

Caro17 — Thu, 05 Nov 2020 09:48:03 GMT

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

Re: Comma after a number

Hanno — Thu, 05 Nov 2020 10:02:14 GMT

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

Re: Comma after a number

Kurt_Bremser — Thu, 05 Nov 2020 10:07:20 GMT

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.

Re: Comma after a number

PeterClemmensen — Thu, 05 Nov 2020 10:42:56 GMT

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;

Re: Comma after a number

Caro17 — Thu, 05 Nov 2020 12:39:53 GMT

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

Re: Comma after a number

Kurt_Bremser — Thu, 05 Nov 2020 12:52:53 GMT

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!).

Re: Comma after a number

Caro17 — Thu, 05 Nov 2020 14:22:07 GMT

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.

Re: Comma after a number

Caro17 — Thu, 05 Nov 2020 14:36:34 GMT

And in excel I do not need the column heading.