BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
turcay
Lapis Lazuli | Level 10

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.
1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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

15 REPLIES 15
Reeza
Super User
Try a data step instead of SQL join.
turcay
Lapis Lazuli | Level 10

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
Lapis Lazuli | Level 10

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
Jade | Level 19

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
Lapis Lazuli | Level 10

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
Tourmaline | Level 20

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
Lapis Lazuli | Level 10

 

@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
Lapis Lazuli | Level 10

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
Super User

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
Lapis Lazuli | Level 10

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.

ChrisNZ
Tourmaline | Level 20

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

turcay
Lapis Lazuli | Level 10

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
Tourmaline | Level 20

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
Lapis Lazuli | Level 10

Oh thanks a lot. I'm glad now 🙂

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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