Hello everyone,
I created a sample data set and code as below. If I reach my aim , the following code can meet my requirement. I prepared a canard score calculation. My main question is how can I multiplex the Score variable for each Questionnaire variables. I shared the desired output as below. I also wonder , is there any other methods which is more easier to get &Mylist's macro without any Transpose step. If you find out more shorter methods on my code, could you tell me and share me the shorter methods, please ? And let's move over the my sample,please !
DATA Have;
LENGTH ID $ 15 Questionnaire1 8 Questionnaire2 8 Questionnaire3 8 Value 8;
INFILE DATALINES MISSOVER;
INPUT ID Questionnaire1 Questionnaire2 Questionnaire3 Value;
DATALINES;
TUR1 7 6 7 2
TUR5 9 4 3 3
TUR10 8 3 9 1
;
RUN;
/*I did this TRANSPOSE step because I need to take all Questionnaire variables in one macro variable
Is there any shorter method ???*/
PROC TRANSPOSE DATA=Have
OUT=Intermediate
Name=Variable;
ID ID;
VAR Questionnaire1 Questionnaire2 Questionnaire3;
RUN; QUIT;
PROC SQL NOPRINT;
SELECT Variable INTO : Mylist Separated By ' '
FROM Intermediate;
QUIT; %PUT &Mylist;
%LET Word_Cnt=%SYSFUNC(countw(&Mylist)); %PUT &Word_Cnt;
/*My purpose is to multiplex to Score variable for each Questionnaire variables*/
%MACRO Have(Var);
PROC SQL;
CREATE TABLE Want(DROP=Sum&Var.) AS
SELECT
*,
SUM(&Var) AS Sum&Var.,
Calculated Sum&Var. *Value AS Score
FROM Have;
QUIT;
%MEND Have;
%MACRO Loop;
%DO i=1 %TO &Word_Cnt;
%Have(%scan(&Mylist.,&i.,%STR( )));;
%END;
%MEND Loop;
%Loop;
1. It looks like you do the transpose only to get the value of the variables. You can query SASHELP.VCOLUMN instead to get the variable names.
proc sql noprint;
select name into :var_list separated by " "
from sashelp.vcolumn
where memname=upper('have') and libname=upper('work')
and upper(name) like upper('Question%');
quit;
%put &var_list;
Here's your macro code modified to give you the results you wanted. It is matrix math and better in IML, in my opinion.
Basically, you are overwriting your table each macro call and losing all the previous results. Here's a way to accomplish that.
DATA Have;
LENGTH ID $ 15 Questionnaire1 8 Questionnaire2 8 Questionnaire3 8 Value 8;
INFILE DATALINES MISSOVER;
INPUT ID Questionnaire1 Questionnaire2 Questionnaire3 Value;
DATALINES;
TUR1 7 6 7 2
TUR5 9 4 3 3
TUR10 8 3 9 1
;
RUN;
/*I did this TRANSPOSE step because I need to take all Questionnaire variables in one macro variable
Is there any shorter method ???*/
PROC TRANSPOSE DATA=Have OUT=intermediate Name=Variable;
ID ID;
VAR Questionnaire1 Questionnaire2 Questionnaire3;
RUN;
QUIT;
PROC SQL NOPRINT;
SELECT Variable INTO : Mylist Separated By ' ' FROM intermediate;
QUIT;
%PUT &Mylist;
data want0;
set have;
run;
%LET Word_Cnt=%SYSFUNC(countw(&Mylist));
%PUT &Word_Cnt;
/*My purpose is to multiplex to Score variable for each Questionnaire variables*/
option mprint symbolgen;
%MACRO Have(Var, i);
%let index=%eval(&i-1);
PROC SQL ;
CREATE TABLE Want&i(DROP=Sum&Var.) AS SELECT *, SUM(&Var) AS Sum&Var.,
Calculated Sum&Var. *Value AS Score&i
FROM want&index.;
QUIT;
%MEND Have;
%MACRO Loop;
%DO i=1 %TO &Word_Cnt;
%Have(%scan(&Mylist., &i., %STR( )), &i.);
;
%END;
%MEND Loop;
%Loop;
Hello @Reeza,
I made up the Score calculation. Actually, I have more complicated formula to calculate the Score. I shared these sample because my main question is how to multiplex the Score results for every Questionnaire variable. Does your question is related to multiplex the Score results ? I don't understand what exactly do you mean ?
Hello @Reeza,
Multiplex : Create more than one(Multiple) Score column. Actually, the formula which was given to me is complicated but It includes sum functions and some subqueries. Value is matter and yes I'm using only observation in the current row. I don't exactly understand your code. Where should I put in my code and why you wrote array v(3) value1-value3; there is only one Value variable. The code which I shared in my first message, please consider the Score variables values. These were calculated by Questionnaire3's values. It means there is an overwriting. The macro calculates all Questionnaire variables for Score variable but just show last variable's value. I need to see both Score1, Score2 and Score 3 variables. I'm really tring to find a method If you can help me by over my example I'll be glad.
Thank you.
1. It looks like you do the transpose only to get the value of the variables. You can query SASHELP.VCOLUMN instead to get the variable names.
proc sql noprint;
select name into :var_list separated by " "
from sashelp.vcolumn
where memname=upper('have') and libname=upper('work')
and upper(name) like upper('Question%');
quit;
%put &var_list;
Here's your macro code modified to give you the results you wanted. It is matrix math and better in IML, in my opinion.
Basically, you are overwriting your table each macro call and losing all the previous results. Here's a way to accomplish that.
DATA Have;
LENGTH ID $ 15 Questionnaire1 8 Questionnaire2 8 Questionnaire3 8 Value 8;
INFILE DATALINES MISSOVER;
INPUT ID Questionnaire1 Questionnaire2 Questionnaire3 Value;
DATALINES;
TUR1 7 6 7 2
TUR5 9 4 3 3
TUR10 8 3 9 1
;
RUN;
/*I did this TRANSPOSE step because I need to take all Questionnaire variables in one macro variable
Is there any shorter method ???*/
PROC TRANSPOSE DATA=Have OUT=intermediate Name=Variable;
ID ID;
VAR Questionnaire1 Questionnaire2 Questionnaire3;
RUN;
QUIT;
PROC SQL NOPRINT;
SELECT Variable INTO : Mylist Separated By ' ' FROM intermediate;
QUIT;
%PUT &Mylist;
data want0;
set have;
run;
%LET Word_Cnt=%SYSFUNC(countw(&Mylist));
%PUT &Word_Cnt;
/*My purpose is to multiplex to Score variable for each Questionnaire variables*/
option mprint symbolgen;
%MACRO Have(Var, i);
%let index=%eval(&i-1);
PROC SQL ;
CREATE TABLE Want&i(DROP=Sum&Var.) AS SELECT *, SUM(&Var) AS Sum&Var.,
Calculated Sum&Var. *Value AS Score&i
FROM want&index.;
QUIT;
%MEND Have;
%MACRO Loop;
%DO i=1 %TO &Word_Cnt;
%Have(%scan(&Mylist., &i., %STR( )), &i.);
;
%END;
%MEND Loop;
%Loop;
Hello @Reeza,
I think I'm the happiest man now 🙂
For the first information thanks a lot It was important for me. I couldn't exactly understand the Dictionary Tables. Do education (Programming 1 & 2)books are include Dictonary tables ? I found a related link like -> http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/viewer.htm#a001385596.htm .I need to read all writing in the page. I couldn't get the why we use the Upper function. Maybe this can be the reason -> The DICTIONARY information for LIBNAME and MEMNAME values is stored in uppercase. If you supply values for LIBNAME and MEMNAME values in uppercase; that is, if you remove the UPCASE function, the WHERE clause will be optimized and performance will be improved. In the previous example, the code would be changed to where libname='WORK' .
Can you take me a suggestion or Web Site link which includes examples, to understand Dictonary tables ?
For my macro question, thanks again I understand in a good way.
I just want to ask %STR() function I read it on Macro education book I couldn't properly get it ?
Now I'll try on my real data I hope I can execute on my real data and my real Score formula. I would like to ask similar questions when I face a problem in the future but sometimes I'm thinking you find some of the questions redundant . Is everything ok for you?
Thank you 🙂
Hello @Reeza
Thank you, It is nice to know , ask my questions whatever I want. but I didn't exactly understand your " incredibly inefficient way to solve your problem " sentence ? Are you talking about this discussion or general ?
Thanks.
Your problem restated:
I want to take the sum of each question variable and multiple that by each observation value. This can be accomplished via matrix multiplication (3X1 * 1*3 = 3x3) or a proc means and multiplication. Both are illustrated below.
There's no need for macros in your solution, except perhaps a macro variable to determine the indices.
None of these may meet your needs if your question/formula is more complex, but there may be other variants that would work.
Using IML:
proc iml;
*Load dataset into IML;
use have;
read all;
*create matrix;
x = Q1 || Q2 || Q3;
*Create array;
y = Value;
*Add up all columns;
sum_x = x[+,];
*Multiply ranges;
y = y * sum_x;
*Export results to a dataset;
create c var {v1 v2 v3};
append from Y;
quit;
data want;
merge have c;
run;
proc print data=want;
run;
And using BASE SAS:
/*BASE SAS Solution*/
proc means data=have stackods noprint;
var q1-q3;
output out=sum_x (drop = _type_ _freq_) sum=sq1-sq3;
run;
data want2;
if _n_=1 then set sum_x;
set have(keep= q1-q3 value);
array v(3) v1-v3;
array sq(3) sq1-sq3;
do i=1 to 3;
v(i)=sq(i)*value;
end;
run;
Hello @Reeza,
I'm surprised again, thank for your detailed information and providing codes. I'm not familiar with PROC IML. Are there any books like "Learning IML by Example" to learn PROC IML. As far as I remember, @Rick_SAS wrote some books related to PROC IML. Maybe he can lead me about this case. What are the advantages of IML and I wonder are there any relationship between R language and IML
I run your code on SAS EG 7.11 environment I don't know why I get the following errors. Maybe it is related to my lack of knowledge about IML.
ERROR: (execution) Matrix has not been set to a value.
ERROR: Number of columns in y does not match with the number of variables in the data set.
ERROR: Variable Q1 not found.
ERROR: Not all variables in the list q1-q3 were found.
Thank you.
Hello @Reeza,
Yes this question is now fully answered 🙂 I edited the your IML code and it works. Thanks again
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.