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.
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.
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?
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
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: