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

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:

ClientResults
A112
A114
A111
A115
B110
B113
B114
C116
C114
C112
C111

Client Final

A1     12:14:11:15

B1    10:13:14

C1     16:12:14:12:11

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

11 REPLIES 11
stat_sas
Ammonite | Level 13

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;

Steelers_In_DC
Barite | Level 11

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.);

Tom
Super User Tom
Super User

CATX(':',of COL:)

data_null__
Jade | Level 19

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;
stat_sas
Ammonite | Level 13

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

Tom
Super User Tom
Super User

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.

stat_sas
Ammonite | Level 13

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

ballardw
Super User

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.

twildone
Pyrite | Level 9

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

 

WARNING: Defining an array with zero elements.

ballardw
Super User

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:

twildone
Pyrite | Level 9

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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 11 replies
  • 1470 views
  • 8 likes
  • 6 in conversation