topic Re: Take all cells values from another tables in SAS Programming

Take all cells values from another tables

turcay — Mon, 21 Sep 2015 17:52:35 GMT

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.

Re: Take all cells values from another tables

Reeza — Mon, 21 Sep 2015 20:07:32 GMT

Try a data step instead of SQL join.

Re: Take all cells values from another tables

turcay — Mon, 21 Sep 2015 22:06:46 GMT

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;

Re: Take all cells values from another tables

turcay — Tue, 22 Sep 2015 06:18:32 GMT

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;

Re: Take all cells values from another tables

andreas_lds — Tue, 22 Sep 2015 08:13:36 GMT

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.

Re: Take all cells values from another tables

turcay — Tue, 22 Sep 2015 08:33:35 GMT

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;

Re: Take all cells values from another tables

turcay — Tue, 22 Sep 2015 08:56:12 GMT

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.

Re: Take all cells values from another tables

turcay — Tue, 22 Sep 2015 09:19:10 GMT

@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 ?

Re: Take all cells values from another tables

Reeza — Tue, 22 Sep 2015 15:39:04 GMT

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.

Re: Take all cells values from another tables

ChrisNZ — Wed, 23 Sep 2015 04:29:05 GMT

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;

Re: Take all cells values from another tables

turcay — Sun, 27 Sep 2015 17:17:17 GMT

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.

Re: Take all cells values from another tables

ChrisNZ — Sun, 27 Sep 2015 23:19:56 GMT

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

Re: Take all cells values from another tables

turcay — Mon, 28 Sep 2015 10:16:01 GMT

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.

Re: Take all cells values from another tables

ChrisNZ — Tue, 29 Sep 2015 03:33:12 GMT

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;

Re: Take all cells values from another tables

turcay — Tue, 29 Sep 2015 10:55:10 GMT

Oh thanks a lot. I'm glad now 🙂

Re: Take all cells values from another tables

ChrisNZ — Wed, 30 Sep 2015 10:12:03 GMT

Note that when doing the merge

  merge TABLE2
        TABLE(in=Y);

you are matching one to one, so the shortest table (in terms of number of observations) stops the processing even if the other one is longer.