topic Re: Proc transpose + Proc tabulate in SAS Data Management

Proc transpose + Proc tabulate

CarolBarahona — Mon, 19 Jun 2017 16:29:17 GMT

Hi all!

I'm confused between to procedure that I have to do. I think first I have to transpose my dataset and then calculate the frequency by proc tabulate, but I have not idea how to do it.

My dataset is something like this, but a little bit more complicated:

(every row is independient)

Year1	Year2	Year3	Gender
1A	1A	1A	A
3A			A
	2A	3A	B
2A	3A	3A	B
1A		1A	A

And I need create something like this:

Gender	Year	Var	Frequency
A	Year1	1A	2
A	Year1	2A	0
A	Year1	3A	1
A	Year2	1A	1
A	Year2	2A	0
A	Year2	3A	0
A	Year3	1A	2
A	Year3	2A	0
A	Year3	3A	0
B	Year1	1A	0
B	Year1	2A	1
B	Year1	3A	0
B	Year2	1A	0
B	Year2	2A	1
B	Year2	3A	1
B	Year3	1A	0
B	Year3	2A	0
B	Year3	3A	2

It's meant tabulate the frequency for every combination of my variables (gender, year and "var").

Thank's! 🙂

Re: Proc transpose + Proc tabulate

art297 — Mon, 19 Jun 2017 17:18:17 GMT

Here is a way to do it using a combination of proc format, proc transpose, two datasteps and proc means:

data have;
  input (Year1 Year2 Year3 Gender) ($);
  cards;
1A 1A 1A A
3A . . A
. 2A 3A B
2A 3A 3A B
1A . 1A A
;

proc format;
  value $gender
  'A'='A'
  'B'='B'
  ;
  value $year
  'Year1'='Year1'
  'Year2'='Year2'
  'Year3'='Year3'
  ;
  value $var
  '1A'='1A'
  '2A'='2A'
  '3A'='3A'
  ;
run;

data need;
  set have;
  recnum=_n_;
run;

proc transpose data=need out=need
   (drop=recnum rename=(_name_=year col1=Var));
  var Year1 Year2 Year3;
  by recnum Gender;
run;

data need;
  set need;
  count=1;
run;

PROC MEANS DATA=need completetypes nway NOPRINT;
  FORMAT gender $gender. year $year. var $var.;
  CLASS gender year var/preloadfmt;
  VAR_count;
  OUTPUT OUT=want (drop=_ :)  N=count;
RUN;

Art, CEO, AnalystFinder.com

Re: Proc transpose + Proc tabulate

Ksharp — Tue, 20 Jun 2017 14:12:56 GMT

data have;
  input (Year1 Year2 Year3 Gender) ($);
  cards;
1A 1A 1A A
3A . . A
. 2A 3A B
2A 3A 3A B
1A . 1A A
;
data temp;
 set have;
 array x{*} $ _character_;
 do i=1 to dim(x);
  name=vname(x{i});
  value=x{i};
  if not missing(value) then output;
 end;
 drop i;
run;
proc freq data=temp noprint;
table name*value/out=want sparse list nocum nopercent;
run;

Re: Proc transpose + Proc tabulate

Ksharp — Tue, 20 Jun 2017 14:20:48 GMT

There is a problem in my above code.   use      this :  



data have;
  input (Year1 Year2 Year3 Gender) ($);
  cards;
1A 1A 1A A
3A . . A
. 2A 3A B
2A 3A 3A B
1A . 1A A
;
data temp;
 set have;
 array x{*} $ year:;
 do i=1 to dim(x);
  name=vname(x{i});
  value=x{i};
  if not missing(value) then output;
 end;
 drop i;
run;
proc freq data=temp noprint;
table gender*name*value/out=want sparse list nocum nopercent;
run;