append analytical result from CORR procedure to single data sets

[Chang]

hi,

I would like to append data of 3 ODS tables from CORR procedure. Data from Cronbach coefficient alph appends nicely in the dataset out._corr_cronbach_append from all the macro calls, as can be seen in the following screenshot:

Data from the other two ODS tables, however, have some missing values.

Missing values begin to show from the second macro call, though data look normal in the first macro call. All these three ODS tables are output somehow in the same way. I have no idea why simple stats and pearson correlation appear differently from Cronbach coefficient alpha. What I want is as seen as Cronbach's alpha. Data (Tab delimited txt) and the script file are attached. My code as the following:

/*create blank data sets for holding data from each macro call*/
data	out._corr_simpleStats_append
	out._corr_cronbach_append
	out._corr_pearson_append
		;
	test='delete this observation';  
run;

%SYSMACDELETE pearsonCorr_Cronbach;
%macro pearsonCorr_Cronbach(var1=,var2=);
	%let data=			out.twin_data_one_file	;
	%let by_group= 	ZYGOSITY_a	;

	proc sort 	data=&data. 
			out= &data._sorted; 
		by &by_group.; 
	run;

	/*export simple stat and pearson correlation*/
	proc corr 	data=&data._sorted
			NOMISS /*exclude missing values in the var variables*/						
			Pearson ; 
		by &by_group.;
		var &var1. &var2.;
		ods output SimpleStats= out._corr_SimpleStats	;
		ods output PearsonCorr=	out._corr_PearsonCorr	; 
	run;
	
	/*export Cronbach’s coefficient alpha*/
	proc corr 	data=&data._sorted
			NOMISS /*exclude missing values in the var variables*/
			alpha	/*computes Cronbach’s coefficient alpha*/
			; 		
		var &var1. &var2.;		
		ods output CronbachAlpha= out._corr_cronbachAlpha;
	run;

	/*append Simple Statistics from each iteration*/
	data out._corr_simpleStats_append;
		retain test ZYGOSITY_a Variable NObs Mean StdDev;
		length Variable $ 20;
		set	out._corr_simpleStats_append		
				out._corr_SimpleStats(keep= ZYGOSITY_a Variable NObs Mean StdDev in=a);
		if a then do;
			test="simple statistics";
			rename NObs= Individuals;		
		end;
		if test=:'delete' then delete;
		format Mean StdDev 5.3  ;
	run;

	/*append Pearson Correlation Coefficients and P value from each iteration*/
	data out._corr_pearson_append; 
		retain test ZYGOSITY_a Variable &var1. P&var1.;
		length Variable $ 20;
		set	out._corr_pearson_append
				out._corr_PearsonCorr	(	where= (Variable= "&var2." )  /*double quote to get value*/
														keep= ZYGOSITY_a Variable &var1. P&var1.														
														in=a	); /*no quoted macro for macro variables*/ 
		if a then do; 
			test='Pearson correlation ';
			rename &var1.= PearsonCorr	P&var1.=pValue;	
		end;
		if test=:'delete' then delete;
		label P&var1.='pValue';	
		format &var1. 5.3 P&var1. pValue6.4; 		
	run;

	/*append Cronbach Coefficient Alpha from each iteration*/
	data out._corr_cronbach_append;
		retain test Variable Variables Alpha;
		length Variable $ 20;
		set	out._corr_cronbach_append
				out._corr_cronbachAlpha(	keep= Variables Alpha in=a);
		if a then do;
			test="Cronbach's alpha";
			Variable="&var1."	; /*			rename Alpha=CronbachCoefAlpha	;	*/
		end;
		if test=:'delete' then delete;		
		format Alpha 6.3;
		keep test Variable Variables Alpha;
	run;

%mend	pearsonCorr_Cronbach;

/*calling the macro*/
%pearsonCorr_Cronbach(var1=PSYCH6_T01 		,var2=PSYCH6_T02);
%pearsonCorr_Cronbach(var1=PSYCH6_IRT_T01	,var2=PSYCH6_IRT_T02);
%pearsonCorr_Cronbach(var1=SOMA6_T01 		,var2=SOMA6_T02);
%pearsonCorr_Cronbach(var1=SOMA6_IRT_T01 	,var2=SOMA6_IRT_T02);
%pearsonCorr_Cronbach(var1=SPHERE_sum_T01	,var2=SPHERE_sum_T02);
%pearsonCorr_Cronbach(var1=SPHERE12_IRT_T01	,var2=SPHERE12_IRT_T02);

 Thanks.

[Reeza]

I don't know what your question is...you have three tables that have different formats and want to append them?

What do you want the final output to look like?

[Chang]

hi Reeza,

What I want is to append the result, exported by ODS output, to the 3 data sets that I create. Results from all the macro calls should be in the same data sets, as shown belows

[FreelanceReinh · Accepted solution]

Hi @Chang,

The issue is that you try to apply the RENAME statement conditionally, which doesn't work, because RENAME is a declarative statement, not an executable statement. You must have got warnings in the log, e.g.

WARNING: Variable Individuals already exists on file WORK._CORR_SIMPLESTATS_APPEND.

SAS, however, performed the renaming, which meant to replace the existing values of, e.g., variable INDIVIDUALS by the missing values of variable NOBS (which exists in the program data vector from the beginning, even though it is contained only in the second dataset in the SET statement). This is why you get all those missing values.

To fix the issue quickly you can use the RENAME= dataset option on the second dataset in the SET statement (and delete the RENAME statement): 

_corr_SimpleStats(keep= ZYGOSITY_a Variable NObs Mean StdDev rename=(NObs=Individuals) in=a);

and analogously in the other data step:  

keep= ZYGOSITY_a Variable &var1. P&var1. rename=(&var1.=PearsonCorr P&var1.=pValue)

As a consequence, you have to replace all other occurrences of the old variable names in the respective data step with the new names (except in the KEEP= dataset option), i.e. NObs in the RETAIN statement becomes Individuals and analogously in the RETAIN statement of the other data step, where also the LABEL and FORMAT statements are affected:

  label pValue='pValue'; 
  format PearsonCorr 5.3 pValue pValue6.4;

For future "appendations" I recommend that you read the documentation of the APPEND procedure.