Hello,
I have two tables as Main and Change as you can see below. First, I change the values of the Current column in the Change table into the ones in the Want column and I overwrite the table. Then I get the score using the formulas below. There are two complicated situations here. Think of a table with 40 questions. How can I apply it to this? And second one is that I want to have in the Main table the column values that exist before I overwrite in the Main table and the values that exist after I put them in the Change table. Theres a similar image below. I want to have Score columns next to them as well.
data Main;
length RefID $ 7 Default 8 Question1 8 Question2 8 Question3 8 Qual 8;
infile datalines missover dlm=",";
input RefID Default Question1 Question2 Question3 Qual;
datalines;
RefID1,0,3,2,2,4
RefID2,0,3,1,3,4
RefID3,1,3,1,3,4
RefID4,0,3,3,3,4
RefID5,0,3,2,1,4
RefID6,1,3,2,3,4
RefID7,0,3,1,2,4
RefID8,1,3,2,3,4
RefID9,0,2,3,4,4
RefID10,0,3,1,4,4
RefID11,1,3,2,2,4
RefID12,1,2,3,2,4
RefID13,0,3,3,3,4
RefID14,0,3,2,4,4
;
run;
data Change;
length Variable $ 20 Want 8 Current 8;
input Variable $ Want Current;
datalines;
Question2 1 2
Question3 1 4
;
run;
data _null_;
set Change;
if _n_ = 1 then call execute('Proc sql;');
call execute(catx(" ", 'update Main set', Variable, '=', Want, 'where', Variable, '=', Current, ';'));
run;
PROC SORT DATA=Main;
BY Question1;
RUN;
proc sql;
create table Main2 as
SELECT
NT.*,
SUM(NT.ObservationNumber) AS TotalObservationNumber,
(NT.ObservationNumber/Calculated TotalObservationNumber) AS NumericRate,
(NT.DefaultNumber/NT.ObservationNumber) as DefaultRate
from
(
select
Question1 as Category,
COUNT(Question2) AS ObservationNumber,
Sum(Default) AS DefaultNumber
from work.Main group by Question1) AS NT;
quit;
proc sql;
Create table work.Main3 as
Select
*,
sqrt((2500/sum((N.YDSum**2)*N.NumericRate))) AS F,
(N.DefaultRate-N.Y) * Calculated F AS Score
FROM(SELECT
NumericRate,
DefaultRate,
(NumericRate*DefaultRate) AS YRow,
SUM(NumericRate*DefaultRate) AS Y,
(SUM(NumericRate*DefaultRate)-DefaultRate) AS YDSum from work.Main2) as N;
QUIT;
I want to have Score columns next to them as well.
... View more
