Transposing columns into row and counting total values and specific values in two separate columns

[Akter]

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!

[qoit · Accepted solution]

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

[andreas_lds]

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;

[Akter]

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.

[Ksharp]

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;

[Akter]

All of your (Ksharp, Andreas, qoit) program worked perfectly! You guess are amazing!
I really appreciate your kind support! There are a lot to learn from you all.
Thank you!😊