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

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!
 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

4 REPLIES 4
Cynthia_sas
SAS Super FREQ
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
wikich
Calcite | Level 5

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.

Kurt_Bremser
Super User

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.

wikich
Calcite | Level 5

Thank you KurtBremser.

Your mehod works.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 2636 views
  • 1 like
  • 3 in conversation