topic Re: Proc report in SAS Programming

Proc report with many like named columns

Ronein — Tue, 27 Oct 2020 09:24:15 GMT

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;

Re: Proc report

LinusH — Tue, 27 Oct 2020 09:02:37 GMT

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).

Re: Proc report with many like named columns

Kurt_Bremser — Tue, 27 Oct 2020 09:35:30 GMT

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.

Re: Proc report with many like named columns

PaigeMiller — Tue, 27 Oct 2020 10:56:47 GMT

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-task-to-create/m-p/672346

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

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.

Re: Proc report

ballardw — Tue, 27 Oct 2020 15:43:34 GMT

@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.

Re: Proc report with many like named columns

Tom — Tue, 27 Oct 2020 16:46:36 GMT

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

define mon_: / display ;

Re: Proc report with many like named columns

Ronein — Wed, 28 Oct 2020 07:01:37 GMT

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;

Re: Proc report with many like named columns

Kurt_Bremser — Wed, 28 Oct 2020 08:27:26 GMT

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.

Re: Proc report with many like named columns

PaigeMiller — Wed, 28 Oct 2020 11:31:38 GMT

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)

Re: Proc report with many like named columns

PaigeMiller — Wed, 28 Oct 2020 11:38:24 GMT

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.

Re: Proc report with many like named columns

Kurt_Bremser — Wed, 28 Oct 2020 12:05:41 GMT

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.