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?
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...
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...
OR if you count first and then transpose you get what you need in a more concise way.
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;
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;
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.
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 |
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.
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 |
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:
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.