turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Take all cells values from another tables

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-21-2015 01:52 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-28-2015 11:33 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-21-2015 04:07 PM

Try a data step instead of SQL join.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-21-2015 06:06 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-22-2015 02:18 AM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-22-2015 04:13 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-22-2015 04:56 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-23-2015 12:28 AM - edited 09-23-2015 12:29 AM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-22-2015 05:19 AM

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 ?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-22-2015 04:33 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-22-2015 11:39 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-27-2015 01:17 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-27-2015 07:19 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-28-2015 06:16 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-28-2015 11:33 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-29-2015 06:55 AM

Oh thanks a lot. I'm glad now