BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Akter
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
qoit
Pyrite | Level 9

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

View solution in original post

5 REPLIES 5
qoit
Pyrite | Level 9

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
Jade | Level 19

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
Obsidian | Level 7

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
Super User
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
Obsidian | Level 7
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!😊

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 5 replies
  • 1403 views
  • 1 like
  • 4 in conversation