Could you pls help me how can i get the 'data want' table from the 'data have' table. In my original data, I've column q1-q16. below is a part of the dataset.
data have;
q1 q2 q3 q4 q5
200 200 200 200 200
200 100 . 200 200
200 200 100 100 200
100 100 200 200 200
In data want, i want to count all values (200 and 100) and put in the total (n) column and 200 in Success(x) by questions in a tabular format.
data want;
ques total(n) Success (x)
q1 4 3
q2 4 2
q3 3 2
q4 4 3
q5 4 4
Thank you all for your support!
Bit of a drag but below should do the job and clean after too:
data have;
input q1-q5;
datalines;
200 200 200 200 200
200 100 . 200 200
200 200 100 100 200
100 100 200 200 200
;
run;
%macro test;
%macro _;
%mend _;
%local i j;
%do i = 1 %to 5; /* or 16 */
data q&i._;
set have (keep=q&i.);
n_ = n(q&i.);
if q&i. >= 200 then
s_ =1;
else s_= 0;
run;
proc sql;
create table q&i. as
select "q&i." as ques,
sum(n_) as n,
sum(s_) as s
from q&i._;
quit;
%end;
data want;
set
%do j = 1 %to 5;
q&j.
%end;
;
run;
proc datasets lib=work nodetails nolist;
delete q:;
run;
quit;
%mend;
%test
Bit of a drag but below should do the job and clean after too:
data have;
input q1-q5;
datalines;
200 200 200 200 200
200 100 . 200 200
200 200 100 100 200
100 100 200 200 200
;
run;
%macro test;
%macro _;
%mend _;
%local i j;
%do i = 1 %to 5; /* or 16 */
data q&i._;
set have (keep=q&i.);
n_ = n(q&i.);
if q&i. >= 200 then
s_ =1;
else s_= 0;
run;
proc sql;
create table q&i. as
select "q&i." as ques,
sum(n_) as n,
sum(s_) as s
from q&i._;
quit;
%end;
data want;
set
%do j = 1 %to 5;
q&j.
%end;
;
run;
proc datasets lib=work nodetails nolist;
delete q:;
run;
quit;
%mend;
%test
No need for macro-code:
data transposed;
set have;
length question $ 2 success 8;
array questions[5] q1-q5;
do i = 1 to dim(questions);
if not missing(questions[i]) then do;
question = vname(questions[i]);
success = (questions[i] = 200);
output;
end;
end;
drop q1-q5 i;
run;
proc summary data=transposed nway;
class question;
var success;
output out=want(drop= _type_ _freq_) n= total sum=success;
run;
Hi andrease,
I liked your program😊. However i want to add additional sex column and want the output with additional 4 columns by Girls and Boys as written in Data 'Want 'below.
data have;
input q1-q5 Sex;
datalines;
200 200 200 200 200 B
200 100 . 200 200 G
200 200 100 100 200 B
100 100 200 200 200 G
;
run;
data want;
Ques total success Girl_tot Girl_Sucess Boy_tot Boy_Sucess
q1 4 3 2 1 2 2
q2 4 2 2 0 2 2
q3 3 2 1 1 2 1
q4 4 4 2 2 2 2
Thank you in advance for your help.
data have;
input q1-q5;
datalines;
200 200 200 200 200
200 100 . 200 200
200 200 100 100 200
100 100 200 200 200
;
run;
proc transpose data=have(obs=0) out=temp;
var _all_;
run;
proc sql noprint;
select cat('n(',_name_,') as ',_name_) into : total separated by ',' from temp;
select cat('sum(',_name_,'=200) as ',_name_) into : success separated by ',' from temp;
create table want as
select 'total' as v, &total from have
union
select 'success' as v,&success from have;
quit;
proc transpose data=want out=final_want;
id v;
run;
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.