Help using Base SAS procedures

Column Transpose

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

Column Transpose

Hi Folks,

Data Class;

input Name $ Sex $;

Cards;

John M

Ruby F

Smith M

Dolly F

Cathy F

run;

Output:

NameSex
JohnM
RubyF
SmithM
DollyF
CathyF

So I am trying to get the following output:

NameMF
John10
Ruby01
Smith10
Dolly01
Cathy01

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


Accepted Solutions
Solution
‎01-23-2012 08:37 AM
Respected Advisor
Posts: 3,777

Re: Column Transpose

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;

View solution in original post


All Replies
PROC Star
Posts: 7,363

Column Transpose

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
N/A
Posts: 1

Re: Column Transpose

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();

Solution
‎01-23-2012 08:37 AM
Respected Advisor
Posts: 3,777

Re: Column Transpose

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;

Contributor
Posts: 24

Column Transpose

Thank you... Works great ..

Respected Advisor
Posts: 3,777

Column Transpose

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.

Contributor
Posts: 24

Column Transpose

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 174 views
  • 3 likes
  • 4 in conversation