Take all cells values from another tables

[turcay]

Hello everyone,

I have a trouble. As you know  i have a table which includes Q's and RefId's. I change column's names then i use proc means procedure and  find the Mean Slope Stddev and Center values. Then i try to find score's by using formulas below. The biggest issue is x value has to come from 'table' table for every Q cells. I mean RefID has to include different value for every Q cells then i'll join Score value's again. The table is below which i try to do. I tried to do something but i failed. Could you help me , please ?

Formula :

CASE WHEN (Slope*(Center-x))<700 Then

(1/EXP(Slope*(Center-x))-Mean)/Stddev*50

Else (0-Mean)/Stddev*50 end

data table;
length ID $ 7 Q1 8 Q2 8 Q3 8 Q4 8 Q5 8 Q6 8;
infile datalines missover dlm=",";
input ID Q1 Q2 Q3 Q4 Q5 Q6 ;
datalines;
RefID1,0.90,0.80,0.00,0.90,0.00,0.70
RefID2,0.100,0.100,0.00,0.70,0.00,60
RefID3,0.40,0.80,0.00,0.90,0.00,0.50
RefID4,0.55,0.80,0.05,0.90,0.00,0.69
RefID5,0.00,0.80,0.60,0.90,0.20,0.90
RefID6,0.96,0.00,0.40,0.90,0.00,0.95
RefID7,0.00,0.80,0.90,0.90,0.00,0.99
RefID8,0.56,0.80,0.55,0.90,0.00,0.93
RefID9,0.99,0.80,0.99,0.90,0.00,0.70
RefID10,0.89,0.88,0.56,0.90,0.00,0.00
;
data join;
input variable $ percentage;
cards;
Q1 0.9
Q2 0.8
Q4 0.2
Q6 0.5
;
run;
proc sql noprint;
 select cats(variable,'=',variable,'_') into : list separated by ' '
  from join;
quit;
proc datasets library=work nodetails nolist;
 modify table;
 rename &list ;
quit;
proc means data=WORK.table STACKODS
FW=12
     PRINTALLTYPES
     CHARTYPE
     QMETHOD=OS
     VARDEF=DF 
           MEAN 
           STD 
           MODE    
           P10 
           P90 ;
var Q:;
ods output summary=stacked;
run;
proc sql noprint;
create table Q1 as 
select 

B.*,

(CASE WHEN (Slope*(Center-Q1_))<700 Then
(1/EXP(Slope*(Center-Q1_))-Mean)/Stddev*50
Else (0-Mean)/Stddev*50 end) as Q1Score
from 
(select 
Variable, 
Mean, 
StdDev,
(2.95/(((P90-mode)*2+mode)-(mode))) as Slope,
(((P90-mode)*2+mode)-((mode)-(P90-mode)*2)/2) as Center
from work.stacked) as A, table B
where a.Variable="Q1_"
;
quit;

Thank you.

[Reeza]

Try a data step instead of SQL join.

[turcay]

I guess i'm so close 🙂

%let variable=Q:; 

data work.score;
	merge work.stacked work.table;
Slope= 2.95/(((P90-mode)*2+mode)-(mode));
Center=((P90-mode)*2+mode)-((mode)-(P90-mode)*2)/2;
IF (Slope*(Center-Q1_))<700 Then Q1Score=(1/EXP(Slope*(Center-Q1_))-Mean)/Stddev*50;
Else Q1Score=(0-Mean)/Stddev*50;
keep ID &variable Q1Score;
run;

[turcay]

I think i need nested IF-ELSE statement and loop for all Q columns.

%let variable=Q:; 
%macro procfreq(Var);
data work.score;
merge work.stacked work.table;
Slope= 2.95/(((P90-mode)*2+mode)-(mode));
Center=((P90-mode)*2+mode)-((mode)-(P90-mode)*2)/2;
IF
(IF (Slope*(Center-Q1))<700 Then Score=(1/EXP(Slope*(Center-Q1))-Mean)/Stddev*50;
Else Score=(0-Mean)/Stddev*50);
ELSE IF
(IF (Slope*(Center-Q1_))<700 Then Score=(1/EXP(Slope*(Center-Q1_))-Mean)/Stddev*50;
Else Score=(0-Mean)/Stddev*50);keep ID &variable Score ;
run;
%mend;

%macro loop;
%let i=1;
%do %while(%scan(&variable.,&i.,%STR( ))~=);
%procfreq(%scan(&variable.,&i.,%STR( )));
%let i=&i.+1;
%end;
%mend;
%loop;

[andreas_lds]

Some issues:

• macro "procfreq" always overwrites the dataset work.score
• the parameter "Var" is unused
• replace the "nested" if:

if (Slope*(Center-Q1))<700 then do;
	Score=(1/EXP(Slope*(Center-Q1))-Mean)/Stddev*50;
end;
else do;
	if (Slope*(Center-Q1_))<700 then do;
		Score=(1/EXP(Slope*(Center-Q1_))-Mean)/Stddev*50;
	end;
	else do;
		Score=(0-Mean)/Stddev*50);
	end;
end;

keep ID &variable Score ;

• maybe replacing the macro-loop with a datastep array brings you closer to the required output.

[turcay]

I appreciate that your replying. Thank you @andreas_lds. But it didn't work. We have to change location of IF. The biggest problem is we have two Qs.

"Q"s and "Q_"s. First the loop has to check column name. If column name is "Q1"or "Q2" or "Q3" then it will perform

IF (Slope*(Center-Q1))<700 Then
Score=(1/EXP(Slope*(Center-Q1))-Mean)/Stddev*50;
Else Score=(0-Mean)/Stddev*50);

If Column name  is "Q1_" or "Q2_" or "Q3_" then it will perform

IF (Slope*(Center-Q1_))<700 Then
Score=(1/EXP(Slope*(Center-Q1_))-Mean)/Stddev*50;
Else Score=(0-Mean)/Stddev*50);

 I generated a variable. It includes stacked tables variables column values. The values are ("Q1"or "Q1_") and ("Q2" or "Q2_")

So first IF loop will check "value" variable's values then it will perform the formula's.

value=variable;

Second main problem is if i succeed how can i do this dynamically.

I hope i can understand my aim.

Thank you.

[ChrisNZ]

Your description is rather confused. 

Function vvaluex can often replace macros in simple cases like this where you just want flexibilty on variable names.

Maybe something like this will help, I just take the value of whatever Q variable is there. Please adapt to your needs.

data HAVE1;
  length SLOPE CENTER MEAN STDDEV Q1 Q2 Q3 8; 
run; 

data HAVE2;
  length SLOPE CENTER MEAN STDDEV Q1_ Q2_ Q3_ 8; 
run;  

data WANT;
  if 0 then set HAVE1 HAVE2;
  set HAVE1;
  do COLNB= 1 to 3; 
    COLVAL=coalesce( vvaluex(cats('Q',COLNB)), vvaluex(cats('Q',COLNB,'_')) ) ;
    SCORE=ifn( SLOPE*(CENTER-COLVAL)<700 , (1/EXP(SLOPE*(CENTER-COLVAL))-MEAN)/STDDEV*50
                                         ,                            (0-MEAN)/STDDEV*50 );
  end;
run;

[turcay]

@andreas_lds wrote:

• macro "procfreq" always overwrites the dataset work.score

.

---

And i want to ask you, how can i prevent to macro's overwrite i want to append it row-by-row ?

[turcay]

I'm trying to do nested if but i didn't succeed. And i changed the formula.

data work.score;

merge work.table (in = x) work.stacked (in = y) ;

value=variable;

if x = 1;

Upper=((P90-MODE)/(P90-P10))*Stddev+Mode;

Lower=((Mode-P10)/(P90-P10))*Stddev+Mode;

Center=(Upper+Lower)/2;

Slope=2.95/(Upper-Center);

IF value='Q1' then valueq=value;

(

IF (Slope*(Center-valueq))<700 Then

Score=(1/EXP(Slope*(Center-valueq))-Mean)/Stddev*50;

Else Score=(0-Mean)/Stddev*50);

ELSE IF value='Q1_' then Score=value;

(IF (Slope*(Center-valueq))<700 Then

Score=(1/EXP(Slope*(Center-valueq))-Mean)/Stddev*50;

Else Score=(0-Mean)/Stddev*50);

keep ID valueq Score ;

run;

[Reeza]

Have you looked into PROC STDIZE at all? 

Also, I don't think your code will do what you want, you merge in data but don't use it.

Please post expected output and the formula's and we can probably help you there.

[turcay]

Actually, these tables are sample tables. Normally, there are hundreds of Q columns. At the First table, i have an ID column and i have six Q columns without  underscore (_). After that columns pass the several conditions(You can assume that “join” tables values passed the condition).If columns values provide this conditions I am adding underscore and overwrite on my main table(“Table”)( as Q1_,Q2_,Q4_,Q6_).Then i find the score results by using formulas. But as you see, i have difficulty figure out how can i develop algorithm to apply formulas both Qs and Q_s. I want to see data sets as follows .

And other columns ....

Thank you.

[ChrisNZ]

Sorry, clarity hasn't improved at all, for me at least.

[turcay]

Actually i did it what i was trying to do. Maybe you can understand now, what i want to do.Are there any posibilities to get this solution appyling different way.

data table;
length ID $ 7 Q1 8 Q2 8 Q3 8 Q4 8 Q5 8 Q6 8 Q7 8 Q8 8 Q9 8 Q10 8;
infile datalines missover dlm=",";
input ID Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10;
datalines;
RefID1,0.90,0.80,0.00,0.90,0.00,0.70,0.90,0.70,0.30,0.00
RefID2,0.100,0.100,0.00,0.70,0.00,60,0.20,0.20,0.40,0.80
RefID3,0.40,0.80,0.00,0.90,0.00,0.50,0.10,0.10,0.60,0.90
RefID4,0.55,0.80,0.05,0.90,0.00,0.69,0.40,0.30,0.50,0.99
RefID5,0.00,0.80,0.60,0.90,0.20,0.90,0.50,0.20,0.10,0.80
RefID6,0.96,0.00,0.40,0.90,0.00,0.95,0.20,0.70,0.90,0.87
RefID7,0.00,0.80,0.90,0.90,0.00,0.99,0.10,0.60,0.10,0.99
;
data join;
input variable $ percentage;
cards;
Q1 0.9
Q2 0.8
Q4 0.2
Q6 0.5
;
run;
proc sql noprint;
 select cats(variable,'=',variable,'_') into : list separated by ' '
  from join;
quit;
proc datasets library=work nodetails nolist;
 modify table;
 rename &list ;
quit;
Proc means data=table STACKODS noprint
FW=12
PRINTALLTYPES
CHARTYPE
QMETHOD=OS
VARDEF=DF
	MEAN
	STD
	MODE
	P10
	P90 ;
VAR Q:;
ODS OUTPUT Summary=table2;
run;

proc sql noprint;
select Variable into : list separated by ' '
  from table2;
quit;


%macro soru(Var);

data table;
MERGE table2(in=x) table(in=y);
if y=1;
Upper=((P90-MODE)/(P90-P10))*STDdev+MODE;
Lower=((Mode-P10)/(P90-P10))*STDdev+MODE;
Center=(Upper+Lower)/2;
Slope=2.95/(Upper-Center);

IF (Slope*(Center-&Var))<700 Then Score&Var=(1/(1+EXP(Slope*(Center-&Var)))-Mean)/Stddev*50;
else Score&Var=(0-Mean)/Stddev*50;
if Score&Var = . then Score&Var = 0;
keep ID Q: Score: ;
run;

%mend;

%macro loop;
%let i=1;
%do %while(%scan(&list.,&i.,%STR( ))~=);
%soru(%scan(&list.,&i.,%STR( )));
%let i=&i.+1;
%end;
%mend;

%loop;

Thank you. 

[ChrisNZ · Accepted solution]

Like this?

%macro soru;

%local i var;

data TABLE;
  merge TABLE2
        TABLE(in=Y);
  if Y;
  UPPER =((P90-MODE)/(P90-P10))*STDDEV+MODE;
  LOWER =((MODE-P10)/(P90-P10))*STDDEV+MODE;
  CENTER=(UPPER+LOWER)/2;
  SLOPE =2.95/(UPPER-CENTER);

%do i=1 %to %sysfunc(countw(&list.,%str( )~=));
  %let var=%scan(&list.,&i.,%str( )~=);
  SCORE&var.= ifn( SLOPE*(CENTER-&var.)<700 , (1/(1+EXP(SLOPE*(CENTER-&var.)))-MEAN)/STDDEV*50
                                            ,                               (0-MEAN)/STDDEV*50 );
  if SCORE&var. = . then SCORE&var. = 0;
%end;

  keep ID Q: SCORE: ;
run;

%mend; 
%soru;

[turcay]

Oh thanks a lot. I'm glad now 🙂