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

Outcome.png

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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;

View solution in original post

13 REPLIES 13
Reeza
Super User
Sorry, what's the math for Score1?

If it's matrix algebra you should look into if IML is appropriate for your analysis.
turcay
Lapis Lazuli | Level 10

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 ?

Reeza
Super User
What does multiplex mean? How the formula is calculated is important...do other observations come into play, does the value matter, are you using only observations in the current row. More than likely all you need is an array but I can't say for sure without knowing the calculation.

array q(3) question1-question3;
array v(3) value1-value3;

*loop over all question variables;
do i=1 to 3;
v(i)=q(i)-3;
end;

turcay
Lapis Lazuli | Level 10

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.

Reeza
Super User

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

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 🙂

Reeza
Super User
You can ask whatever you want. I don't find your questions clear. You found the answer to your question, but it's probably an incredibly inefficient way to solve your problem.
turcay
Lapis Lazuli | Level 10

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.

Reeza
Super User

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

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.

Reeza
Super User
I renamed your variables from Question1-Question3 to Q1-Q3 so I didn't have to type the full value outs. Long names leads to typos and errors that are hard to find. Change the names to match your data and you'll get the same results. There are PROC IML tutorials, but I don't know IML.

R and IML are similar in that they treat data as matrix/arrays which is closer to matrix algebra. You can call R from IML if desired.

Anyways I think this question is now fully answered.
turcay
Lapis Lazuli | Level 10

Hello @Reeza,

Yes this question is now fully answered 🙂 I edited the your IML code and it works. Thanks again

Reeza
Super User
There isnt a general solution to your problem. You need to provide specifics, especially if you have subqueries. Or at least not one I know.

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
  • 13 replies
  • 1853 views
  • 4 likes
  • 2 in conversation