topic Re: Transposing columns into row and counting total values and specific values in two separate colum in SAS Programming

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

Akter — Thu, 01 Apr 2021 02:07:51 GMT

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!

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

qoit — Thu, 01 Apr 2021 04:14:05 GMT

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

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

andreas_lds — Thu, 01 Apr 2021 05:06:39 GMT

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;

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

Ksharp — Thu, 01 Apr 2021 11:07:54 GMT

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;

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

Akter — Thu, 01 Apr 2021 14:10:42 GMT

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!😊

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

Akter — Thu, 01 Apr 2021 15:28:35 GMT

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.