topic Column Transpose in SAS Procedures

Column Transpose

Raghs_Newbee — Mon, 23 Jan 2012 10:36:23 GMT

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

Column Transpose

art297 — Mon, 23 Jan 2012 13:03:37 GMT

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

Re: Column Transpose

GDP — Mon, 23 Jan 2012 13:16:19 GMT

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;s1))) from class order by sex ;Data y (drop =_BREAK_);Set x;%do I = 1 %to %eval(&amp;s1);

rename c&amp;i. = &amp;&amp;s&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();

Re: Column Transpose

data_null__ — Mon, 23 Jan 2012 13:37:22 GMT

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;

Column Transpose

Raghs_Newbee — Mon, 23 Jan 2012 14:48:25 GMT

Thank you... Works great ..

Column Transpose

data_null__ — Mon, 23 Jan 2012 15:15:00 GMT

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';

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

Column Transpose

Raghs_Newbee — Mon, 23 Jan 2012 15:32:39 GMT

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