Help using Base SAS procedures

transpose - help

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

transpose - help

Hello,

I have dataset (ID, Property), example

ID Property

1 Blue

1 Green

2 Red

2 Yellow

2 Blue

I want to transform Property column to row.

If some property belongs to ID, its value in output will be 1, otherwise 0.

desired output

ID Red Green Blue Yellow

1     0     1        1      0   

2     1     0        1      1

I tried to use TRANSPOSE procedure, but unsuccessfully.

In MS excel I can use it by contingent table with count function, but in SAS I'm still lamer.


Can someone help me how to do this transposition?


Accepted Solutions
Solution
‎01-17-2013 07:16 AM
Respected Advisor
Posts: 3,777

Re: transpose - help

If you use transpose you will need a variable to transpose with the value 1.  WIth transpose you don't get a zero but that can be fixed.  Also transpose with duplicates with need a bit of extra processing.  As an alternative you can do this...

data property;
   input id property $;
   cards;
1 Blue
1 Green
2 Red
2 Yellow
2 Blue
3 Blue
3 Green
3 Red
3 Yellow
3 Blue
;;;;
   run;
proc transreg data=property;
   model class(property / zero=sum) /  cprefix=0;
  
output design out=property2(drop=_: intSmiley Happy;
   id id;
   run;
  
quit;
proc print;
  
run;
proc summary data=property2 nway;
  
class id;
   output out=pcountmax /*sum?*/ max(blue--yellow)=;
   run;
Proc print;
  
run;

View solution in original post


All Replies
Solution
‎01-17-2013 07:16 AM
Respected Advisor
Posts: 3,777

Re: transpose - help

If you use transpose you will need a variable to transpose with the value 1.  WIth transpose you don't get a zero but that can be fixed.  Also transpose with duplicates with need a bit of extra processing.  As an alternative you can do this...

data property;
   input id property $;
   cards;
1 Blue
1 Green
2 Red
2 Yellow
2 Blue
3 Blue
3 Green
3 Red
3 Yellow
3 Blue
;;;;
   run;
proc transreg data=property;
   model class(property / zero=sum) /  cprefix=0;
  
output design out=property2(drop=_: intSmiley Happy;
   id id;
   run;
  
quit;
proc print;
  
run;
proc summary data=property2 nway;
  
class id;
   output out=pcountmax /*sum?*/ max(blue--yellow)=;
   run;
Proc print;
  
run;
Respected Advisor
Posts: 3,777

Re: transpose - help

OR if you count first and then transpose you get what you need in a more concise way.

proc summary data=property nway completetypes;
  
class id property;
   output out=pcount(drop=_type_);
   run;
proc print;
  
run;
proc transpose data=pcount out=widecount;
   by id;
   var _freq_;
   id property;
   run;
proc print;
  
run;
Contributor
Posts: 45

Re: transpose - help

Hi Julob,

I hope you don't mind my bringing a DATA step solution on a PROCEDURES forum.

DN's proc solutions are very straightforward and succinct.

Just as an academic exercise, I tried how a DATA step approach would look like. Here is one, using regular arrays, hash objects, and data access functions.

I find data access functions very convenient whenever data constants need to become variables somewhere in a program:

data _null_;

%let names="Red","Green","Blue","Yellow";

     length id 8;

     id=0;Red=0; Green=0; Blue=0; Yellow=0;

     declare hash wd(ordered:"a");

     wd.defineKey("id");

     wd.defineData("id",&names);

     wd.defineDone();

     dsid=open("tall");  

     nrows=attrn(dsid,"NOBS");

     array nx{4} _temporary_ (0,0,0,0);

     do r=1 to nrows;

          rf=fetchobs(dsid,r);

          id=getvarn(dsid,1);

          rcf=wd.find();

          nx(1)=Red;nx(2)=Green;nx(3)=Blue;nx(4)=Yellow;

          name=getvarc(dsid,2);

          x=whichc(name, &names);

          nx(x)=1;

          rc=wd.replace(key:id,data:id,data:nx(1),data:nx(2),data:nx(3),data:nx(4));

     end;

     wd.output(dataset:"wide");

     stop;

run;

Contributor
Posts: 45

Re: transpose - help

I should mention that in this particular instance, I could have done away with data access functions:

data _null_;

%let names="Red","Green","Blue","Yellow";

     length id 8;

     id=0;Red=0; Green=0; Blue=0; Yellow=0;

     declare hash wd(ordered:"a");

     wd.defineKey("id");

     wd.defineData("id",&names);

     wd.defineDone();

     array nx{4} _temporary_ (0,0,0,0);

     do until(done);

          set tall end=done;

          rcf=wd.find();

          nx(1)=Red;nx(2)=Green;nx(3)=Blue;nx(4)=Yellow;

          x=whichc(property, &names);

          nx(x)=1;

          rc=wd.replace(key:id,data:id,data:nx(1),data:nx(2),data:nx(3),data:nx(4));

     end;

     wd.output(dataset:"wide");

     stop;

run;

Respected Advisor
Posts: 3,777

Re: transpose - help

joehinson wrote:

I should mention that in this particular instance, I could have done away with data access functions:

In what "particular instance" would you need data access functions?  I'm not familiar with "replacing" SMUM (set merge update modify) with data acess function as you are showing here.

Contributor
Posts: 45

Re: transpose - help

DN,

I find that, in my hands, data access functions allow for greater processing of rows, columns, and variable names, especially for intricate transpositions.

An example is given below (recently offered on SAS-L):

data wide;

input id client_pre_b1 client_post_b1 caregiver_pre_b1 caregiver_post_b1;

datalines;

1   3                 2             3              3

2   4                 2             2              1

3   2                 1             .              .

;

run;

data tall(keep=id b1 trial respond);

       dsid=open("wide");

       nrows=attrn(dsid,"NOBS");

       ncols=attrn(dsid,"NVARS");

       do r=1 to nrows;

              rf=fetchobs(dsid,r);

              id=getvarn(dsid,1);

              do c=2 to ncols;

                     b1=getvarn(dsid,c);

                     vx=varname(dsid,c);

                     tag1=scan(vx,1,"_");

                     tag2=scan(vx,2,"_");

                     trial=(whichc(tag2,"pre","post")-1);

                     respond=(whichc(tag1,"client","caregiver")-1);

                     output;

              end;*for c;

       end;*for r;

stop;

run;

RESULTS:

  

  id b1 trial respond
1 1 3 0 0
2 1 2 1 0
3 1 3 0 1
4 1 3 1 1
5 2 4 0 0
6 2 2 1 0
7 2 2 0 1
8 2 1 1 1
9 3 2 0 0
10 3 1 1 0
11 3 . 0 1
12 3 . 1 1

Respected Advisor
Posts: 3,777

Re: transpose - help

I'm not trying to cause trouble because I really do want to know where this can be used where regular SMUM cannot.


You example is simply extracting data from meta data (variable names) and that can be more easily done after transposing.

proc transpose data=wide out=tall2 prefix=b;
   by id;
   run;
data tall2;
   set tall2;
   trial   = ifn(indexw(_name_,"pre",'_'),0,1);
   respond = ifn(indexW(_name_,'client','_'),0,1);
   drop _name_;
   run;
Obs
idb1trialrespond

1

1300
21210
31301
41311
52400
62210
72201
82111
93200
103110
113.01
123.11
Contributor
Posts: 45

Re: transpose - help

Quite impressive, DN.

I hope I didn't create the impression that there were situations one had to ABSOLUTELY use data access functions. Perhaps I should just have emphasized convenience and greater flexibility. Naturally, any data access function solution can be replicated also with plain DATA step.

Incidentally, a paper was once presented about the issue:

http://www.nesug.org/proceedings/nesug07/cc/cc15.pdf

SAS Super FREQ
Posts: 8,743

Re: transpose - help

Hi:

  do you need an output DATASET or just an output REPORT???? You could use PROC TABULATE and/or PROC REPORT for this, easily. This program uses the PROPERTY data already posted.

cynthia


ods html file='c:\temp\report_crosstab.html';
options missing=0;
proc tabulate data=property missing;
  title '1) PROC TABULATE';
  class id property;
  table id,
        property*n=' ';
run;

proc report data=property missing nowd;
  title '2) PROC REPORT';
  column id n,property;
  define id / group;
  define property / across;
  define n / ' ';
run;

ods html close;

☑ This topic is SOLVED.

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

Discussion stats
  • 9 replies
  • 365 views
  • 4 likes
  • 4 in conversation