BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
julob
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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=_: int:);
   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

9 REPLIES 9
data_null__
Jade | Level 19

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=_: int:);
   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;
data_null__
Jade | Level 19

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;
joehinson
Calcite | Level 5

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;

joehinson
Calcite | Level 5

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;

data_null__
Jade | Level 19

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.

joehinson
Calcite | Level 5

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

data_null__
Jade | Level 19

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
joehinson
Calcite | Level 5

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

Cynthia_sas
SAS Super FREQ

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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