## Take all cells values from another tables

Solved
Super Contributor
Posts: 413

# Take all cells values from another tables

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.

Accepted Solutions
Solution
‎09-29-2015 06:54 AM
PROC Star
Posts: 2,321

## Re: Take all cells values from another tables

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;``````

All Replies
Super User
Posts: 23,661

## Re: Take all cells values from another tables

Try a data step instead of SQL join.
Super Contributor
Posts: 413

## Re: Take all cells values from another tables

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;``````
Super Contributor
Posts: 413

## Re: Take all cells values from another tables

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;``````
Valued Guide
Posts: 557

## Re: Take all cells values from another tables

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.
Super Contributor
Posts: 413

## Re: Take all cells values from another tables

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.

PROC Star
Posts: 2,321

## Re: Take all cells values from another tables

[ Edited ]

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;``````

Super Contributor
Posts: 413

## Re: Take all cells values from another tables

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 ?

Super Contributor
Posts: 413

## Re: Take all cells values from another tables

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;

Super User
Posts: 23,661

## Re: Take all cells values from another tables

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.

Super Contributor
Posts: 413

## Re: Take all cells values from another tables

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.

PROC Star
Posts: 2,321

## Re: Take all cells values from another tables

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

Super Contributor
Posts: 413

## Re: Take all cells values from another tables

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.

Solution
‎09-29-2015 06:54 AM
PROC Star
Posts: 2,321

## Re: Take all cells values from another tables

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;``````
Super Contributor
Posts: 413

## Re: Take all cells values from another tables

Oh thanks a lot. I'm glad now

🔒 This topic is solved and locked.