Column Transpose

[Raghs_Newbee]

Hi Folks,

Data Class;

input Name $ Sex $;

Cards;

John M

Ruby F

Smith M

Dolly F

Cathy F

run;

Output:

Name	Sex
John	M
Ruby	F
Smith	M
Dolly	F
Cathy	F

So I am trying to get the following output:

Name	M	F
John	1	0
Ruby	0	1
Smith	1	0
Dolly	0	1
Cathy	0	1

I have shown just an Example, Just like M or F - The column can contain 'N' number of Values. So all these should be taken as Variables and produce the given output.

I dont want this code  because I cant search and group all observations to write an If Clause.

Data Just (Drop=Sex);

set Class ;

if Sex = 'M' then Male = 1; else Male = 0;

if Sex = 'F' then Female = 1; else Female = 0;

run;

Thanks,

Raghuraman Ramesh

[art297]

I haven't tried the following, myself, but it may present a reasonable approach to your problem: http://www.sas.com/offices/NA/canada/downloads/presentations/VancouverMay11/Variable.pdf

[GDP]

Try this!!!

Try this !!!!!

Options missing = 0;

proc report data = class out =x ;

Column name sex;

define sex / across ;

run;

%macro s();

Proc SQL; select count(distinct sex) into : s from class;

Proc SQL; select distinct sex into : s2-:s%sysfunc(trim(%eval(&amp;amp;s1))) from class order by sex ;</p><p>Data y (drop =_BREAK_);</p><p>Set x;</p><p>%do I = 1 %to %eval(&amp;amp;s1);

rename c&amp;amp;i. = &amp;amp;&amp;amp;s&amp;amp;i.;

%end;

run;

%mend ;

%s();

correct one !!

options missing=0;

proc report data= class out=x  ;

column name sex;

define sex / across  ;

run;

options symbolgen;

%macro s();

proc sql; select count(distinct sex) into : s from class;

proc sql; select distinct sex into :s2-:s%sysfunc(trim(%eval(&s+1))) from class order by sex ;

data y (drop=_BREAK_);

set x;

%do i = 1 %to %eval(&s+1);

rename c&i. = &&s&i.;

%end;

run;

%mend;

%s();

[data_null__ · Accepted solution]

Data Class;
   input Name $ Sex $;
   Cards;
John M
Ruby F
Smith M
Dolly F
Cathy F
   run;
proc summary nway completetypes;
   class name sex / order=data;
   output out=freq(drop=_type_);
   run;
proc transpose;
   by name notsorted;
   id sex;
   var _freq_;
   run;
proc print;
   run;

[Raghs_Newbee]

Thank you... Works great ..

[data_null__]

It would be helpful to know more about your data.  I usually do not use order=data but I wanted the output in the same order as you specified.  If you have non-unique NAMEs us BY NAME notsorted; CLASS SEX / preloadfmt order=data; 

proc format; value $sex(notsorted) 'M'='M' 'F'='F'; 

OR

skip the SUMMARY and transpose a var with value=1; and poke the zeros in later.

[Raghs_Newbee]

You are right, I used Proc SQL to get the same data that proc summary fetched me. So I used proc Transpose with the tables that i created using the proc SQL. The only difference I find between Datasets generated from Proc SQL and Proc Summary is - In Proc Summary Blanks are replaced with ZERO but that is not the case in Proc SQL.

Thanks a ton, You have been great.

Raghuraman Ramesh