SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Proc transpose + Proc tabulate

Reply
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)

 

 

Year1Year2Year3Gender
1A1A1AA
3A  A
 2A3AB
2A3A3AB
1A 1AA

 

And I need create something like this:

 

GenderYearVarFrequency 
AYear11A2
AYear12A0
AYear13A1
AYear21A1
AYear22A0
AYear23A0
AYear31A2
AYear32A0
AYear33A0
BYear11A0
BYear12A1
BYear13A0
BYear21A0
BYear22A1
BYear23A1
BYear31A0
BYear32A0
BYear33A2

 

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

 

Thank's! Smiley Happy

PROC Star
Posts: 7,467

Re: Proc transpose + Proc tabulate

Posted in reply to CarolBarahona

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

 

Super User
Posts: 10,018

Re: Proc transpose + Proc tabulate

Posted in reply to CarolBarahona
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,018

Re: Proc transpose + Proc tabulate

Posted in reply to CarolBarahona
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;
Ask a Question
Discussion stats
  • 3 replies
  • 177 views
  • 0 likes
  • 3 in conversation