Proc report define name programmatically

[zqkal]

Hello SAS users can you please help me.

I was automating one of my existing daily report.

The report is a historical report which adds daily information inform of an existing report.

I have a SAS data set that has column name such as this “_07_07_2014, _07_04_2014, _07_03_2014, _07_02_2014, _07_01_2014, _06_030_2014”. In the existing report I’m using proc report to define the column name in the report to display it like this “JULY 07, 2014, JULY 04, 2014, JULY 03, 2014, JULY 02, 2014, JULY 01, 2014, JUN 30, 2014’.

Here is the code:

   define _07_07_2014   / display 'JULY 07,2014' format=comma12.;

   define _07_04_2014   / display 'JULY 04,2014' format=comma12.;

   define _07_03_2014   / display 'JULY 03,2014' format=comma12.;

   define _07_02_2014   / display 'JULY 02,2014' format=comma12.;

   define _07_01_2014   / display 'JULY 01,2014' format=comma12.;

   define _06_30_2014   / display 'JUN 30,2014' format=comma12.;

Every day, I added one more statement to handle the new column added into the report. how can I to this programmatically. So that I don’t have to add new statement every day. 

Does anyone knows how to create title page using SAS ODS pdf.

[RW9]

If you do it that way, you are very quickly going to run out of column width.  Remember there are page width maximums, and if you are going to Excel then column maximums.  You could do it in a datastep call execute:

data _null_;

     call execute('proc report.....;');

     do I='01JAN2014'd to today();

          call execute(' define '||put(I,date9.)||' / "'||put(I,date9.)||'";');

     end;

run;

That will create a define line for each date between 01Jan and now, so something along those lines.  However, again, I would consider re-organising your data/output in a different way.

As for how to create a title page, well that is a whole other story.  There are several topics on this.  Its dependant on your output type, what you want in a contents etc.  Its definitely not a short topic, so please search these forums, also a paper: http://www.nesug.org/Proceedings/nesug06/io/io13.pdf

[Reeza]

I think you're into macro's here no matter if you don't want to re-design your process a bit.

You have two options, 1 is to add a label that shows the date, the second is to generate the proc report code using a macro.

You don't show the rest of your data but another option, without macros, is to flip your data and then use proc report with the across option and this makes it easier to display the values that you'd like to see.

Any of those options sound doable to you?

[Ksharp · Accepted solution]

Yeah, You can query dictionary tables and wrap these variable into a macro variable and use it in proc report :

data have;
input _07_07_2014 _07_04_2014;
cards;
2 3
3 5
;
run;

proc sql;
select name into : name separated by ' ' from dictionary.columns where libname='WORK' and memname='HAVE';
select 'define '||trim(name)||" /display '"||put(input(compress(name, ,'kd'),mmddyy12.),monname3.)||trim(translate(substr(name,5),',','_'))||" ' format=comma12."   into : list separated by ';'
  from dictionary.columns where libname='WORK' and memname='HAVE';
quit;
options mprint mlogic symbolgen ;
proc report data=have nowd;
columns &name ;
&list 
;
run;

Xia Keshan

[zqkal]

Thanks for your quick response. You save me a lots of time by querying the dictionary table