Proc report with many like named columns

[Ronein]

Hello

I want to create proc report.

There are many varaibles that I want to write in define statement.

What is the clever way to write them using macro/array in the define statement instead of typing many rows?

Please note that I have the list of the varaible in  a macro varaible called vector

Data example;
input Subject mon_1701 mon_1702 mon_1703 mon_1704 mon_1705 mon_1706
              mon_1707 mon_1708 mon_1709 mon_1710 mon_1711 mon_1712
              mon_1801 mon_1802 mon_1803 mon_1804 mon_1805 mon_1806
              mon_1807 mon_1808 mon_1809 mon_1810 mon_1811 mon_1812
;
cards;
1 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
2 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
3 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
;
run;

%let vector=mon_1701 mon_1702 mon_1703 mon_1704 mon_1705 mon_1706 
mon_1707 mon_1708 mon_1709 mon_1710 mon_1711 mon_1712
mon_1801 mon_1802 mon_1803 mon_1804 mon_1805 mon_1806 
mon_1807 mon_1808 mon_1809 mon_1810 mon_1811 mon_1812;

%let K_Vector=24;

proc report data=example nowd;
column  Subject
mon_1701 mon_1702 mon_1703 mon_1704 mon_1705 mon_1706
mon_1707 mon_1708 mon_1709 mon_1710 mon_1711 mon_1712
mon_1801 mon_1802 mon_1803 mon_1804 mon_1805 mon_1806
mon_1807 mon_1808 mon_1809 mon_1810 mon_1811 mon_1812
;
define Subject / display  'Subject #';
define mon_1701 / display ;
define mon_1702/ display  ;
.................
.................
define mon_1812/ display  ;
Run;

/****My try to do it*/
/****My try to do it*/
/****My try to do it*/
%let vector=mon_1701 mon_1702 mon_1703 mon_1704 mon_1705 mon_1706
mon_1707 mon_1708 mon_1709 mon_1710 mon_1711 mon_1712
mon_1801 mon_1802 mon_1803 mon_1804 mon_1805 mon_1806
mon_1807 mon_1808 mon_1809 mon_1810 mon_1811 mon_1812;
%put &vector;

proc report data=example nowd;
column Subject &vector;
define _all_;
Run;

[LinusH]

From my perspective, I think your problem is the data structure.

Most reporting made is easier and syntax more data driven if you have long data sets (transpose columns to rows).

[ballardw]

---

@LinusH wrote:

From my perspective, I think your problem is the data structure.

Most reporting made is easier and syntax more data driven if you have long data sets (transpose columns to rows).

---

Been telling the OP that for a couple years now.

[Kurt_Bremser]

Use a long layout:

data have;
input subject $ mon $ value;
datalines;
A 1701 5
A 1702 7
B 1701 3
B 1702 4
;

proc report data=have;
column subject mon,value;
define subject / group;
define mon /across;
define value / display sum;
run;

As always, a long layout makes the coding dead simple.

[PaigeMiller]

Aggreeing with @LinusH , a long data set would make this simple.

@Ronein , we have discussed this in at least three previous threads, where you were advised to create long rather than wide data sets.

https://communities.sas.com/t5/SAS-Programming/Dynamic-Report-with-non-fix-number-of-columns-and-tas...

https://communities.sas.com/t5/SAS-Programming/proc-report-How-to-write-shorter-code/m-p/671366#M201...

https://communities.sas.com/t5/SAS-Programming/Create-Dynamic-labels-process/m-p/670504#M201298

I urge you to embrace the idea of using long data sets in this situation instead of ignoring it.

[Tom · Accepted solution]

Since they all start with the same string you can just use one DEFINE statement.

define mon_: / display ;

[Ronein]

You are the best!

This is the solution!

I understand that in such case better structure is long and not wide.

However, in this case I receive a data set with wide structure and from this structure I need to create the desired report.

I am writing again the solution that you sent.

Data example;
input Subject mon_1701 mon_1702 mon_1703 mon_1704 mon_1705 mon_1706
              mon_1707 mon_1708 mon_1709 mon_1710 mon_1711 mon_1712
              mon_1801 mon_1802 mon_1803 mon_1804 mon_1805 mon_1806
              mon_1807 mon_1808 mon_1809 mon_1810 mon_1811 mon_1812
;
cards;
1 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
2 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
3 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
;
run;


%let K_Vector=24;
proc report data=example nowd;
column  Subject
mon_1701 mon_1702 mon_1703 mon_1704 mon_1705 mon_1706
mon_1707 mon_1708 mon_1709 mon_1710 mon_1711 mon_1712
mon_1801 mon_1802 mon_1803 mon_1804 mon_1805 mon_1806
mon_1807 mon_1808 mon_1809 mon_1810 mon_1811 mon_1812
;
define Subject / display  'Subject #';
define mon_: / display ;
Run;
 

[Kurt_Bremser]

The first step in any work process should be "make your work easier". So you reduce data in observations and columns to only keep what's needed; or (like I do) you first format the code you get so that it is easier to read and work with. Likewise,

---

However, in this case I receive a data set with wide structure

---

in such a case you first convert to a better structure; this work upfront reduces the time needed later, and the gains usually outweigh the effort you invested at the beginning.

[PaigeMiller]

in such a case you first convert to a better structure; this work upfront reduces the time needed later, and the gains usually outweigh the effort you invested at the beginning.

Great advice! Maybe it should be a maxim! (Or a sub-maxim under 19)

[Kurt_Bremser]

Given the fact that I will retire in 2022 (and if SASGF in San Diego happens, I'll be there as a "retirement gift" to myself!!), it may well be that I spend part of my time making a SAS book out of the Maxims, with a chapter for each. That will allow me to elaborate on each maxim in a much wider sense.

[PaigeMiller]

Allow me to point out that the problem with the solution

define mon_:/display;

is that it does not create nicely labelled columns. The first column will be labelled mon_1701. If you'd prefer to have the column labelled as January 2017 or 01/17 or anything like that, then this means you'd have to return to naming all the columns individually in their own DEFINE statement (or better yet, use a long data set). Sometimes, maybe the naming of columns isn't important, especially if only you are going to view the results; but any time you want to share this result with others, I always provide meaningful names/labels to my columns, as it looks much more professional and results in fewer questions.