ODS output in a macro

[HBa]

i have a macro to run a series of proc mixed on a data base. it basically run the proc mixed on all columns so that i will gain a general idea about the data. when it runs, i like it to create a table as the output, where it reports independent and dependent variables as well as the p values. the macro is attached.

the output table looks like the following:

The SAS System
Obs	Class	Levels	Values	Descr	Value	NumDF	DenDF	FValue	ProbF
1	idsub	20	1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20			.	.	.	.
2	fact	2	NOEC NOEO			.	.	.	.
3	REPT	3	1 2 3			.	.	.	.
4		.		Dependent Variable	test	.	.	.	.
5		.				1	99	29.38	<.0001
6	idsub	20	1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20			.	.	.	.
7	fact	2	NOEC NOEO			.	.	.	.
8	REPT	3	1 2 3			.	.	.	.
9		.		Dependent Variable	MX	.	.	.	.
10		.				1	99	8.5	0.0044
11	idsub	20	1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20			.	.	.	.
12	fact	2	NOEC NOEO			.	.	.	.
13	REPT	3	1 2 3			.	.	.	.
14		.		Dependent Variable	MY	.	.	.	.
15		.				1	99	2.26	0.1356

I am hoping that the output looks somethig like the following:

Obs	Class	Levels	Values	Dependent Variable	NumDF	DenDF	FValue	ProbF
1	idsub	20	1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20	test	1	99	29.38	<.0001
2	fact	2	NOEC NOEO		.	.	.	.
3	REPT	3	1 2 3		.	.	.	.
4	idsub	20	1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20	MX	1	99	8.5	0.0044
5	fact	2	NOEC NOEO
6	REPT	3	1 2 3		.	.	.	.
7	idsub	20	1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20	MY	1	99	2.26	0.1356
8	fact	2	NOEC NOEO		.	.	.	.
9	REPT	3	1 2 3		.	.	.	.

any suggestion on this is highly appreciated.

thanks

HB

[PaigeMiller]

Couldn't you do this as a re-arranging of the final output data set from the macro, rather than a modification inside the macro?

[HBa]

thank you for your comment. yes, that would be possible, but since the macro is going to run many times, it will be a large addition to my code if every time that i run the macro, i include lines in the code to change the format of the macro output.

thanks

HB

[PaigeMiller]

---

@HBa wrote:

thank you for your comment. yes, that would be possible, but since the macro is going to run many times, it will be a large addition to my code if every time that i run the macro, i include lines in the code to change the format of the macro output.

thanks

HB

---

I disagree with this. You need a small addition to the code, that can be placed immediately after the macro, or inside the macro as the last code the macro executes. What difference does it make anyway how large the additional code is anyway, its still one program that you have to run.

[Cynthia_sas]

Hi:
While it was good of you to include your code, it's hard to visualize what your input data looks like. Can you provide some fake data to start with or mock up something similar with SASHELP datasets?

It looks like you know how to use ODS OUTPUT and it seems that you probably are using PROC PRINT after the macro runs or else you want to run PROC PRINT after the macro runs. Is there a way to show. Offhand, it looks like your Dependent Variable information is stuck in data rows and not as a header the way you want. You can probably fix this in one of your DATA step programs. I don't use PROC MIXED or have any examples of multiple datasets needing to be analyzed such as you show, so I don't have any other suggestions.

Cynthia

[HBa]

attached are a portion of my data set and the macro. the following is what i run to print the table.

data VR3;
set save.tempc;
run;

title 'comparison of NOEC and NOEO';
data temp; set temprept;
if fact ne 'NOEC' and fact ne 'NOEO' then delete;
run;
%HBLST(temp)
proc print data=last2;run;

thank you

[ballardw]

Maybe instead of this:

data last;                                                                                                                              
set last tempc&i tempa&i tempb&i;                                                                                                       
 if levels=. and numdf = . and Descr ne 'Dependent Variable' then delete;                                                                                                 
drop Effect;
run; 

You are looking for something more like

data last;
  merge work.tempc&i
        work.tempa&i(where=(descr='Dependent Variable'))
        work.tempb&i (drop=effect)
  ;
  rename value=depvar;
  label value='Dependent variable';
  drop descr;
run;

Merge without a BY variable just matches obs1 to obs1 each set, obs2 to obs2 etc.

[HBa]

thank you for your response. It seems that it works to some level but not completely. when running the proc mixed, it keeps giving a the following error:

ERROR: The variable Value in the DROP, KEEP, or RENAME list has never been referenced.

In my code, the data LAST was created in every iteration of the loop. by writing

" set last tempc&i tempa&i tempb&i; "

i am basically merging the last data set that was created from previous iteration with he new data. when i applied your suggested code, not only i got the above error, the LAST output of the macro had data only for one round of proc mixed.

thanks again,

HB