topic Re: How to transpose data in SAS Programming

How to transpose data

PatrykSAS — Thu, 17 Jun 2021 11:08:30 GMT

Hello

I'd like to transpose my table with counting occurrences.

Source table:

ID	VAR1	VAR2
1	HOME	APPLE
1	AWAY	BANANA
2	HOME	BANANA
2	HOME	APPLE

I wish I get result:

ID	HOME	AWAY	APPLE	BANANA
1	1	1	1	1
2	2	0	1	1

How can I do it?

Re: How to transpose data

tarheel13 — Thu, 17 Jun 2021 11:25:29 GMT

It's not really transposing. You want to get counts within by groups. You could use proc means, proc SQL, proc summary.

data test;
input ID var1 $ var2 $;
cards;
1 HOME APPLE
1 AWAY BANANA
2 HOME BANANA
2 HOME APPLE
;
proc print;
run;

proc sql;
	create table sums as 
	select ID, sum(var1='HOME') as home, sum(var1='AWAY') as away,
	sum(var2='APPLE') as apple, sum(var2='BANANA') as banana
		from test
		group by ID;
quit;

Re: How to transpose data

Kurt_Bremser — Thu, 17 Jun 2021 11:50:01 GMT

Without having any values in code:

data have;
input ID $ VAR1 $ VAR2 $;
datalines;
1 HOME  APPLE
1 AWAY  BANANA
2 HOME  BANANA
2 HOME  APPLE
;

data first;
set have;
array vars {*} var:;
do i = 1 to dim(vars);
  var = vars{i};
  output;
end;
run;

proc freq data=first noprint;
tables id*var / out=want_long;
run;

proc transpose data=want_long out=want (drop=_name_ _label_);
by id;
var count;
id var;
run;