## Proc transpose + Proc tabulate

Occasional Contributor
Posts: 5

# Proc transpose + Proc tabulate

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!

PROC Star
Posts: 8,149

## Re: Proc transpose + Proc tabulate

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.;
VAR_count;
OUTPUT OUT=want (drop=_ :)  N=count;
RUN;
```

Art, CEO, AnalystFinder.com

Super User
Posts: 10,689

## Re: Proc transpose + Proc tabulate

```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;```
Super User
Posts: 10,689

## Re: Proc transpose + Proc tabulate

```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;```
Discussion stats
• 3 replies
• 242 views
• 0 likes
• 3 in conversation