format all column names that are similar

[nesslee]

Hello everyone,

I would like to format all my columns that are number as below:

define MyAmount_201912 / style(column)={tagattr='format:#,##0.00'};

define MyAmount_202001 / style(column)={tagattr='format:#,##0.00'};

etc.

But instead of defining every column name like above (MyAmount_201912, etc.), I would like to say in my coding : do this style for all column names that begin with, or I would like to say format this for all columns that are in number format.

The reason I would like to set this logic is because the names of some columns are calculated and it might be different in each run.

Do you see a way to set this logic?

Thanks

[Ksharp · Accepted solution]

If your SAS is above 9.4 .

define MyAmount_: / style(column)={tagattr='format:#,##0.00'};

[nesslee]

Thank you very much for this tip!

[PaigeMiller]

Do you see a way to set this logic?

Yes, the idea of creating columns in a data set with year and month numbers in the variable name is really the problem. This is completely unnecessary and leads to the problem you are having and should be avoided. If instead, you have a long data set with a variable named MyAccount and many rows where the month and year are actual data values, then you can use the ACROSS option in PROC REPORT for variable MyAccount and it takes care of naming and formatting the resulting columns in the report. SAS has already programmed this, so you don't have to!

So you can save yourself the time and trouble of creating these columns in a wide data set with month and year in the variable name, and simply use a long data set and the ACROSS option to automatically create the desired columns in your report.

And then this problem that you mentioned

The reason I would like to set this logic is because the names of some columns are calculated and it might be different in each run.

goes away completely. Example on made up data (where you can see that this works, regardless of the months in the data set — try it!)

data made_up_data;
	length planet $ 5;
    do i=0 to 11;
		do j=1 to 2;
	     month=intnx('month',today(),-i,'b');
		 planet='Mars';
		 y=rand('normal');
		 output;
		 planet='Earth';
		 y=rand('normal');
		 output;
		 planet='Pluto'; /* Yes, Pluto is a planet */
		 y=rand('normal');
		 output;
		 end;
	end;
	drop i j;
	format month yymm9.;
run;

proc report data=made_up_data;
	columns planet month,y;
	define planet/group;
	define month/across; /* This is the important step, where month is considered an ACROSS variable */
	define y/mean 'Perihelion' format=8.2;
run;

[nesslee]

thank you a lot for your suggestion and example!