topic Re: Appending standardized fields in SAS Procedures

Appending standardized fields

deleted_user — Wed, 28 Jan 2009 13:05:12 GMT

THe SAS help file gives the following example of creating standardized fields with proc standard and then appending them onto the original dataset with proc sql:

proc standard data=score mean=75 std=5 out=stndtest;
var test1 test2;
run;
proc sql;
create table combined as
select old.student, old.studentnumber,
old.section,
old.test1, new.test1 as StdTest1,
old.test2, new.test2 as StdTest2,
old.final
from score as old, stndtest as new
where old.student=new.student;

Anyone know of an easier way to get the standardized fields appended onto the original dataset? The problem is if i have 20 fields then I have to hand-code the sql statement.

Is there a way to either:

- tell proc standard to append the fields onto the original dataset (like proc princomp)

- Maybe an example of referencing (and rename) fields in proc sql using the "--" operator??

Any help would be greatly appreciated - thank you!

Re: Appending standardized fields

data_null__ — Wed, 28 Jan 2009 14:04:16 GMT

this address the following issues

1) variable list of unknown length
2) create new variable with prefix STD
3) combine with original(requires merge key)

It does not create the variable order that was achieved with proc sql but that could be address if desired.

[Pre]
data score;
length Student $ 9;
input Student $ StudentNumber Section $
Test1 Test2 Final @@;
format studentnumber z4.;
datalines;
Capalleti 0545 1 94 91 87 Dubose 1252 2 51 65 91
Engles 1167 1 95 97 97 Grant 1230 2 63 75 80
Krupski 2527 2 80 69 71 Lundsford 4860 1 92 40 86
McBane 0674 1 75 78 72 Mullen 6445 2 89 82 93
Nguyen 0886 1 79 76 80 Patel 9164 2 71 77 83
Si 4915 1 75 71 73 Tanaka 8534 2 87 73 76
;;;;

run;
proc sort;
by studentNumber;
run;
*** Get variables list into SAS data set;
proc transpose data=score(obs=0) out=varlist;
var test1-test2;
run;

*** Gen some code RENAME option and varlist;
%let RenameOpt=;
proc sql;
select
catx('=',_name_,cats('Std',_name_)),
_name_
into :RenameOpt separated by ' ',
:VarList separated by ' '
from varlist
;
quit;
run;
%put NOTE: RenameOpt=&RenameOpt;
%put NOTE: Varlist=&varList;

proc standard
data = score
(
keep = studentnumber &VarList
rename = (&renameOpt)
)
mean=75 std=5 out=stndtest;
var Std:;
run;
data work.Combined;
merge score stndtest;
by studentNumber;
run;
proc print data=combined noobs round;
title 'Standardized Test Scores for a College Course';
run;
[/pre]

Re: Appending standardized fields

data_null__ — Wed, 28 Jan 2009 16:54:04 GMT

This version address the variable order issue. The standardized versions of the variables are woven into the original variables immediately following the unstandardized version of the variables.

This program could be made completly dynamic with a few more parameters say in a macro to specify input and output datasets, std variable list, and merge key. I suppose the merge key could be omitted if you wanted use One-to-One Merging.

[pre]
data score;
length Student $ 9;
input Student $ StudentNumber Section $
Test1 Test2 Final @@;
format studentnumber z4.;
datalines;
Capalleti 0545 1 94 91 87 Dubose 1252 2 51 65 91
Engles 1167 1 95 97 97 Grant 1230 2 63 75 80
Krupski 2527 2 80 69 71 Lundsford 4860 1 92 40 86
McBane 0674 1 75 78 72 Mullen 6445 2 89 82 93
Nguyen 0886 1 79 76 80 Patel 9164 2 71 77 83
Si 4915 1 75 71 73 Tanaka 8534 2 87 73 76
;;;;
run;
proc sort;
by studentNumber;
run;
*** Get variable to score into a SAS data set;
proc transpose data=score(obs=0) out=stdvars(index=(_name_));
var test1-test2;
run;
*** Get _ALL_ variable names into a SAS data set;
proc transpose data=score(obs=0) out=allvars;
var _all_;
run;
*** Weave all vars with STDvar names, to use in retain statement;
data weaveSTDvars;
length _name_ $32;
set allvars(in=in1);
output;
set stdvars key=_name_/unique;
if _iorc_ eq 0 then do;
_name_ = cats('Std',_name_);
output;
end;
else _error_=0;
run;
proc print;
run;
*** Gen some code RENAME option and varlist;
%let RenameOpt=;
%let varlist=;
%let NewVariableOrder=;
proc sql noprint;
select
catx('=',_name_,cats('Std',_name_)),
_name_
into :RenameOpt separated by ' ',
:VarList separated by ' '
from stdvars
;
select _name_ into :newVariableOrder separated by ' '
from weaveSTDvars;
quit;
run;
%put NOTE: RenameOpt=&RenameOpt;
%put NOTE: Varlist=&varList;
%put NOTE: NewVariableOrder=&NewVariableOrder;
proc standard
data = score
(
keep = studentnumber &VarList
rename = (&renameOpt)
)
mean=75 std=5 out=stndtest;
var Std:;
run;
data work.Combined;
retain &NewVariableOrder;
merge score stndtest;
by studentNumber;
run;
proc print data=combined noobs round;
title 'Standardized Test Scores for a College Course';
run;
[/pre]

Re: Appending standardized fields

deleted_user — Wed, 28 Jan 2009 18:27:04 GMT

There's a lot of great techniques here - thanks!