topic Re: transpose - help in SAS Procedures

transpose - help

julob — Thu, 17 Jan 2013 10:17:32 GMT

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?

Re: transpose - help

data_null__ — Thu, 17 Jan 2013 12:16:34 GMT

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;

Re: transpose - help

data_null__ — Thu, 17 Jan 2013 12:22:16 GMT

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;

Re: transpose - help

joehinson — Thu, 17 Jan 2013 16:02:21 GMT

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;

Re: transpose - help

Cynthia_sas — Thu, 17 Jan 2013 17:18:40 GMT

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;

Re: transpose - help

joehinson — Thu, 17 Jan 2013 17:21:20 GMT

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;

Re: transpose - help

data_null__ — Thu, 17 Jan 2013 17:45:07 GMT

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.

Re: transpose - help

joehinson — Thu, 17 Jan 2013 18:04:55 GMT

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

Re: transpose - help

data_null__ — Thu, 17 Jan 2013 19:23:28 GMT

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;

Obsidb1trialrespond
1
1300
21210
31301
41311
52400
62210
72201
82111
93200
103110
113.01
123.11

Re: transpose - help

joehinson — Thu, 17 Jan 2013 19:39:00 GMT

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

	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

	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

	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