topic Re: Transpose in SAS Programming

Re: Transpose

twildone — Fri, 17 Jul 2015 01:24:25 GMT

Hi....I am trying to create a new variable by transposing the original results and join the transposed results together to create the new variable. I understand I can use Proc Transpose to get the results below. Where I am having problem is how to handle the situation when number of new transposed variables varies for each client. Any suggestions would be greatly appreciated.....Thanks.

Original:

Client	Results
A1	12
A1	14
A1	11
A1	15
B1	10
B1	13
B1	14
C1	16
C1	14
C1	12
C1	11

Client Final

A1 12:14:11:15

B1 10:13:14

C1 16:12:14:12:11

Re: Transpose

stat_sas — Fri, 17 Jul 2015 02:21:45 GMT

data have;

input Client $ Results;

datalines;

A1 12

A1 14

A1 11

A1 15

B1 10

B1 13

B1 14

C1 16

C1 14

C1 12

C1 11

;

data want(keep=client final);

set have;

length Final $ 25;

retain final;

by client;

Final=catx(':',final,put(results,2.));

if first.client then final=put(results,2.);

if last.client;

run;

Re: Transpose

ballardw — Fri, 17 Jul 2015 14:29:07 GMT

Since your transpose would create variables such as col1, col2, etc:

data want;

set transposed;

array c col: ;

final = catx(':', of c(*));

run;

which will ignore any of the Col that are blank for a given client.

Re: Transpose

Steelers_In_DC — Fri, 17 Jul 2015 14:29:37 GMT

I did this:

data have;

infile cards dsd;

input Client$ Results$;

cards;

A1,12

A1,14

A1,11

A1,15

B1,10

B1,13

B1,14

C1,16

C1,14

C1,12

C1,11

;

run;

proc transpose data=have out=tran (drop=_NAME_);by client;var results;

data want(keep=client final);

set tran;

Final = catx(':',col1,col2,col3,col4);

run;

Then stepped away and like your solution much better. Can you put into words with this line is doing? if first.client then final=put(results,2.);

Re: Transpose

Tom — Fri, 17 Jul 2015 15:07:28 GMT

CATX(':',of COL:)

Re: Transpose

data_null__ — Fri, 17 Jul 2015 15:31:16 GMT

It is resetting the value of FINAL (retained) for the first.client. It would make more sense to move it up one line and init FINAL to missing. And even better to use DO UNTIL.

data want(keep=client final);
   set have;
   length Final $ 25; 
   retain final;
   by client;
   if first.client then call missing(final);
   Final=catx(':',final,put(results,2.)); 
   if last.client;
   run; 
proc print; 
   run; 

data want(keep=client final);     
   do until(last.client);
      set have;
      by client;
      length Final $ 25; 
      Final=catx(':',final,put(results,2.)); 
      end; 
   run; 
proc print; 
   run;

Re: Transpose

stat_sas — Sat, 18 Jul 2015 00:09:07 GMT

It resets final variable for each client and terminates cumulative process.

Re: Transpose

Tom — Sat, 18 Jul 2015 00:35:48 GMT

It might work but it seems strange to first set FINAL to have the full list from the previous by group plus the first value from this by group and later overwrite it with the first value from this group.

Re: Transpose

stat_sas — Sat, 18 Jul 2015 04:30:03 GMT

Thanks for your input. Agreed, it should be in reverse order with call missing as suggested by data_null_ above.

Re: Transpose

twildone — Tue, 21 Jul 2015 14:20:49 GMT

Hi....I tried your suggestion and I am getting message:

WARNING: Defining an array with zero elements.

Re: Transpose

ballardw — Tue, 21 Jul 2015 16:53:30 GMT

The example I posted assumed you had run the data through Proc Transpose and that the transposed variables were named Col1 Col2 Col3 etc, as the default behavior.

If you transpose and use a different prefix name, use that instead of COL:

Re: Transpose

twildone — Wed, 22 Jul 2015 00:52:09 GMT

Hi Ballardw...It works now.....used "array c c:;" and it worked exactly like you said would....thank you so much...its really appreciated.