BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
zqkal
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

View solution in original post

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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
Super User

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
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 919 views
  • 0 likes
  • 4 in conversation