topic using loop in the define step of proc report in ODS and Base Reporting

using loop in the define step of proc report

wikich — Wed, 13 Feb 2019 05:04:09 GMT

Here is my question:

I want to using loop to repeat define step in proc report.

Here is my example:

*get data;

data mydata;

set sashelp.class;

run;

*rename the columns with regular name, like mycol1, mycol2...;

%macro rename_col(inset=,outset=);

proc sql;

create table rename_table_1 as

select trim(name)as oldname from dictionary.columns

where libname='WORK' and memname=upcase("&inset")

;

quit;

data rename_table_2;

set rename_table_1;

nrow=put(_n_,3.);

newname=compress(cat("mycol",nrow));

run;

proc sql;

select cats(oldname,'=',newname)

into :myrename separated by ' '

from rename_table_2

;

quit;

data &outset;

set &inset.(rename=(&myrename));

run;

%mend rename_col;

%rename_col(inset=mydata,ouset=finaldata);

/*loop define step in proc report, like, define mycol1 / "title1"; define mycol2 / "title2";.....*/

proc report data=finaldata;

array headers(*) $ ("title1" "title2" "title3" "title4" "title5");

array cols(*) (mycol1 mycol2 mycol3 mycol4 mycol5);

do i=1 to dim(headers);

define cols(i) / headers(i);

end;

run;

Finally, the code does not work. Array could not be used proc report, excepting compute block.

Is there another way to implement this function?

Thanks!

Re: using loop in the define step of proc report

Cynthia_sas — Wed, 13 Feb 2019 06:19:37 GMT

Hi:
It is not clear to me what you want to do, or why. The DEFINE statement allows you to provide the column header that you want to use for each item in your COLUMN statement, why not just use that capability.

Although you can use an ARRAY in a COMPUTE block, the ARRAY will NOT touch the values of the COLUMN headers, as you envision. The ARRAY might be used to perform some operation on the column values, but NOT on the column headers. That is the purpose of the permanently defined labels or the use of the label option in the DEFINE statement.

Cynthia

Re: using loop in the define step of proc report

wikich — Wed, 13 Feb 2019 06:34:58 GMT

Thanks for your reply, Cynthia.

I want to write a macro to form a report dynamically with the header I provided.

In my case, I have many table to output. Every table have different header names.

for example, i have a table with 10 columns. If I want to generate a table with specific header manually, I have to write define ../.. 10 times. In addition, I have more than 30 tables like this to export. It is tedious and boring.

So I want to write a macro to deal with such problem.

Re: using loop in the define step of proc report

Kurt_Bremser — Wed, 13 Feb 2019 07:12:27 GMT

Store names and labels in a dataset, and use call execute() from that:

data columns;
input name label;
cards;
mycol1 title1
mycol2 title2
mycol3 title3
mycol4 title4
mycol5 title5
;
run;

data _null_;
set columns end=eof;
if _n_ = 1 then call execute('proc report data=finaldata;');
call execute('define ' !! name !! '/ "' !! trim(label) !! '";');
if eof then call execute('run;');
run;

You will probably expand the data _null_ step so that it also creates the column statement.

Re: using loop in the define step of proc report

wikich — Wed, 13 Feb 2019 07:37:01 GMT

Thank you KurtBremser.

Your mehod works.