topic Re: Proc Transpose Issue in SAS Programming

Proc Transpose Issue

RobertNYC — Tue, 27 Nov 2012 19:21:05 GMT

Hi all--

I'm having trouble with this transpose. When I try to transpose the data set below I get an error message that says duplicate variables in the by group so I need to rename the variables.

Id	Question	responce1	responce2	responce3	responce4	responce5	responce6	responce7
fgg45	S1	YES
fgg45	S2	NO	NO	NO	NO	NO	NO
fgg45	G5	YES	YES	YES	YES	YES	YES	YES
fgg45	G6	NO
gg453	S1	YES	YES
gg453	S2	NO	NO	NO	NO	NO	NO	NO
gg453	G5	YES	YES	YES
gg453	G6	NO	NO	NO	NO

What I need should look like this. I need to add a _#x to the end of each question depending on how many responses there are.

ID	S1_1x	S1_2x	S2_1x	S2_2x	S2_3x	S2_4x	S2_5x	S2_6x	S2_7x
fgg45	YES	NO	NO	NO	NO	NO	NO
gg453	YES	YES	NO	NO	NO	NO	NO	NO	NO

Any assistance is greatly appreciated.

Matt--

Re: Proc Transpose Issue

art297 — Tue, 27 Nov 2012 19:26:46 GMT

Are you NOT interested in the G5 and G6 responses and does it matter whether S1 has two or all seven levels?

Re: Proc Transpose Issue

RobertNYC — Tue, 27 Nov 2012 19:36:22 GMT

Hi Arthur--

Sorry. I should have specified what. I am interested in all of the Question values, I just didn't add G5 and G6 to the sample table. There are actually over 100 questions.

Thanks--

Re: Proc Transpose Issue

art297 — Tue, 27 Nov 2012 19:56:55 GMT

Would something like the following suffice?:

data have;

infile cards truncover;

input (Id Question responce1-responce7) ($);

cards;

fgg45 S1 YES

fgg45 S2 NO NO NO NO NO NO

fgg45 G5 YES YES YES YES YES YES YES

fgg45 G6 NO

gg453 S1 YES YES

gg453 S2 NO NO NO NO NO NO NO

gg453 G5 YES YES YES

gg453 G6 NO NO NO NO

;

proc sort data=have;

by id question;

run;

proc transpose data=have (rename=(responce1-responce7=_1-_7))

out=need;

by id question;

var _1-_7;

run;

data need (drop=question);

set need;

_name_=catt(Question,_name_);

run;

proc transpose data=need out=want;

by id;

var col1;

run;

Re: Proc Transpose Issue

Linlin — Tue, 27 Nov 2012 20:08:32 GMT

Hi Art,

Has SAS agreed to reward you with a lifelong sas license when you get 10,000 points?

Re: Proc Transpose Issue

art297 — Tue, 27 Nov 2012 20:14:36 GMT

: Good question. Haven't heard anything but I would hope that also includes a trip to the next SGF to be formally awarded the prize.

We should know fairly soon as the milestone should be approaching very quickly. Minimally, one would at least expect to see a post from Dr.G

Re: Proc Transpose Issue

Linlin — Tue, 27 Nov 2012 20:20:13 GMT

Agreed! only 5 points away.

Re: Proc Transpose Issue

art297 — Tue, 27 Nov 2012 20:26:03 GMT

: Look again! Someone must have marked one of my posts as being a correct answer.

Re: Proc Transpose Issue

Linlin — Tue, 27 Nov 2012 20:29:54 GMT

Congratulations!!! I am looking for a different status. - Linlin

Re: Proc Transpose Issue

RobertNYC — Tue, 27 Nov 2012 20:58:20 GMT

Thanks Art. Last thing. There aren't necessarily 7 responses in each data set There could be fore or there could be 100 it changes every time.

Re: Proc Transpose Issue

Ksharp — Wed, 28 Nov 2012 05:27:17 GMT

OK. If I understand what you mean.

data have;
  infile cards truncover;
  input (Id Question responce1-responce7 ) ($);
  cards;
fgg45 S1 YES
fgg45 S2 NO NO NO NO NO NO
fgg45 G5 YES YES YES YES YES YES YES
fgg45 G6 NO
gg453 S1 YES YES
gg453 S2 NO NO NO NO NO NO NO
gg453 G5 YES YES YES
gg453 G6 NO NO NO NO
;
run;
proc sql noprint;
create table x as
select question,name,catx('=',name,tranwrd(cats(question,name),'responce','_')) as rename length=100 from
 (select distinct question from have) , 
 (select lowcase(name) as name from dictionary.columns where libname='WORK' and memname='HAVE' and lowcase(name) like 'responce%')
;
quit; 
data temp;
 set x;
 by question;
 length want $ 32767;
 retain want;
 want=catx(' ',want,rename);
 if last.question then do; output;call missing(want);end;
 drop rename name;
run; 
data _null_;
 set temp end=last;
 if _n_ eq 1 then call execute('data want(drop=question); merge ');
 call execute('have(where=(question="'||trim(question)||'") rename=('||trim(want)||')) ');
 if last then call execute(';run;');
 run;

Ksharp

Re: Proc Transpose Issue

joehinson — Wed, 28 Nov 2012 13:51:21 GMT

....and here is another approach that also works for any number of columns.

(I did this mainly as an academic exercise, testing the use of data access functions (eg. getvarc()), and the hash-of-hashes for group processing):

data have;

infile datalines truncover;

input (Id Question responce1-responce7) ($);

datalines;

fgg45 S1 YES

fgg45 S2 NO NO NO NO NO NO

fgg45 G5 YES YES YES YES YES YES YES

fgg45 G6 NO

gg453 S1 YES YES

gg453 S2 NO NO NO NO NO NO NO

gg453 G5 YES YES YES

gg453 G6 NO NO NO NO

;

run;

data _null_;

if _N_=1 then do;

length Responce $3 colx $12;

if(1=2)then set have;

declare hash final (ordered:"a",multidata:"Y");

final.defineKey("id","colx");

final.defineData("id","colx","responce");

final.defineDone();

declare hash ids (ordered:"a");

ids.defineKey("id");

ids.defineData("id", "objQues","hobjQues");

ids.defineDone();

declare hiter hids("ids");

declare hash objQues;

declare hiter hobjQues;

declare hash objResp;

declare hiter hobjResp;

call missing (of _all_);

dsid=open("have"); put dsid=;

nrows=attrn(dsid,"NOBS");put nrows=;

ncols=attrn(dsid,"NVARS");put ncols=;

end;

do y=1 to nrows;

rc=fetchobs(dsid,y);

id=getvarc(dsid,1);question=getvarc (dsid,2);

if ids.find() ne 0 then do;

objQues=_new_ hash(ordered:"a");

objQues.defineKey("id","Question");

objQues.defineData("Question","objResp","hobjResp");

objQues.defineDone();

hobjQues=_new_ hiter("objQues");

rci=ids.replace();

end;

if objQues.find() ne 0 then do;

objResp=_new_ hash(ordered:"a",multidata:"Y");

objResp.defineKey("id","Question");

objResp.defineData("id","Question","colx","Responce");

objResp.defineDone();

hobjResp=_new_ hiter("objResp");

rcq=objQues.replace();

do n=3 to ncols;

cx=varname(dsid,n);

dx=compress(cx,"0123456789","k");

colx=strip(Question)||"_"||"X"||dx;

responce=getvarc(dsid,n);

rcr=objResp.replace();

rcf=final.replace();

end;

final.output(dataset:"finaldata");

stop;

run;

proc transpose data=finaldata out=want;;

by id;

id colx;

var responce;

run;

Re: Proc Transpose Issue

data_null__ — Wed, 28 Nov 2012 14:42:05 GMT

PROC TRANSPOSE has options that allow you to skip the data step to modify the values of _NAME_. It even has an option to add the superfluous X as the end.

I wasn’t sure but you also create variables that are all missing. This can be address with a where statement or
left as is.

data have;
  infile cards missover; 
  input (Id Question responce1-responce7) ($);
  cards; 
fgg45 S1 YES
fgg45 S2 NO NO NO NO NO NO
fgg45 G5 YES YES YES YES YES YES YES
fgg45 G6 NO
gg453 S1 YES YES
gg453 S2 NO NO NO NO NO NO NO
gg453 G5 YES YES YES
gg453 G6 NO NO NO NO
;;;;
   run; 

proc sort data=have;
   by id question;
   run; 

proc transpose 
      data=have(rename=(responce1-responce7=_1-_7))
      out=need;
   by id question;
   var _1-_7;
   run; 

 
proc transpose data=need out=want(drop=_name_) suffix=x;
   where not missing(col1);
   by id;
   var col1;
   id question _name_;
   run; 

Re: Proc Transpose Issue

joehinson — Wed, 28 Nov 2012 14:48:51 GMT

Thanks, DN.

I chose to create labels within the data step because they were needed in the hash-of-hashes processing.

I'm very much aware of those useful features of Proc Transpose. As a matter of fact, if I were not hard pressed for time, I wouldn't have needed Proc Transpose at all.

Regards,

Joe

Re: Proc Transpose Issue

art297 — Wed, 28 Nov 2012 15:22:37 GMT

: Here is yet one more try, this time independent of the number of variables and with a final reordering of the resulting variable names. I did, however, change the spelling of "response". The only thing that would have to be changed is the initial maximum number of response variables if it is ever greater than 999:

data have;

infile cards truncover;

input (Id Question response1-response999) ($);

cards;

fgg45 A1 YES

fgg45 S1 YES

fgg45 S2 NO NO NO NO NO NO

fgg45 G5 YES YES YES YES YES YES YES

fgg45 G6 NO

gg453 S1 YES YES

gg453 S2 NO NO NO NO NO NO NO

gg453 G5 YES YES YES

gg453 G6 NO NO NO NO

;

proc sort data=have;

by id question;

run;

proc transpose data=have

out=need (where=(not missing(col1)));

by id question;

var response:;

run;

data need (drop=question);

set need;

_name_=catt(Question,"_",substr(_name_,anydigit(_name_)));

run;

proc sql noprint;

select _name_ into :reorder

separated by " "

from need

order by _name_

;

quit;

proc transpose data=need out=want (drop=_:);

by id;

var col1;

run;

data want;

retain id &reorder.;

set want;

run;

Re: Proc Transpose Issue

data_null__ — Wed, 28 Nov 2012 15:45:30 GMT

Joe, my comments were for Art's first version of this program not yours.