DATA Step, Macro, Functions and more

Take all cells values from another tables

Accepted Solution Solved
Reply
Super Contributor
Posts: 395
Accepted Solution

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;
 
MainTable.png
ResultTable.png
 
Thank you.

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

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;

View solution in original post


All Replies
Super User
Posts: 19,878

Re: Take all cells values from another tables

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

Re: Take all cells values from another tables

I guess i'm so close Smiley Happy

%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: 395

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

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: 395

Re: Take all cells values from another tables

Posted in reply to andreas_lds

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: 1,760

Re: Take all cells values from another tables

[ Edited ]

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;

 

Super Contributor
Posts: 395

Re: Take all cells values from another tables

Posted in reply to andreas_lds

 

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: 395

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: 19,878

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.

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

 

 

Super Contributor
Posts: 395

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 .

 

istenilen.pngAnd other columns ....

 

 

Thank you.

PROC Star
Posts: 1,760

Re: Take all cells values from another tables

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

Super Contributor
Posts: 395

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: 1,760

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: 395

Re: Take all cells values from another tables

Oh thanks a lot. I'm glad now Smiley Happy

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 15 replies
  • 339 views
  • 1 like
  • 4 in conversation