topic Re: format all column names that are similar in ODS and Base Reporting

format all column names that are similar

nesslee — Wed, 09 Sep 2020 12:20:51 GMT

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

Re: format all column names that are similar

Ksharp — Wed, 09 Sep 2020 12:32:17 GMT

If your SAS is above 9.4 .

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

Re: format all column names that are similar

PaigeMiller — Wed, 09 Sep 2020 13:14:56 GMT

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;

Re: format all column names that are similar

nesslee — Wed, 09 Sep 2020 13:21:42 GMT

Thank you very much for this tip!

Re: format all column names that are similar

nesslee — Wed, 09 Sep 2020 13:22:29 GMT

thank you a lot for your suggestion and example!