Calculating trends

[CynthiaFan]

Hi,

I have a data set (OBS to COL7) listed below and have problem to generate transition and comments through calculating the up and down trend from COL1 to COL7. Any recommendations on programming?

Obs	COL1	COL2	COL3	COL4	COL5	COL6	COL7	transition	comment
44	up	down						1	Transitionaing
45	down	up						1	Transitionaing
46	up	up						2	all the way up
47	down	up	down	up				1	Transitionaing
48	down	down						3	all the way down
51	up	down	up	down				1	Transitionaing
68	up
69	down	down
70	down	up	down
71	down
72	down
73	down	down

Thanks.

Ying

[Astounding · Accepted solution]

There might be more elegant ways, but here is an easy way:

data want;

set have;

length all_cols $ 28  comment $ 15;

all_cols = catt(of col1-col7);

if index(all_cols, 'up') and index(all_cols, 'down') then comment = 'Transitioning';

else if index(all_cols, 'up') then comment = 'all the way up';

else if index(all_cols, 'down') then comment = 'all the way down';

drop all_cols;

run;

[ChrisNZ]

Another way:

data WANT;
  set HAVE;
  if countw(catx(' ',of COL1-COL7)) < 2     then COMMENT = '                ';
  else if index(catt(of COL1-COL7), 'up')   then 
    if    index(catt(of COL1-COL7), 'down') then COMMENT = 'Transitioning   ';
    else                                         COMMENT = 'All the way up  ';
  else if index(catt(of COL1-COL7), 'down') then COMMENT = 'All the way down';
run;

[CynthiaFan]

Thank you for the reply. I can only use CAT in my programming but the syntax works to catch the trend. Another problem is that my several data sets were transposed to with various numbers of col's(col1-col4 or col1-col5). Is that possible to automate that with ARRAY in MACRO? Now I do it in separate MACRO to accommodate the number of col's.

data &d..&a.3;

length comment $ 14.;

set &d..&a.2;

if (index(cat(of col1-col4),"Down")>=1) and (index(cat(of col1-col4),"Up")>=1) then do;

comment="Transitioning";

 end;

else if (index(cat(of col1-col4),"Down")>=1) and (index(cat(of col1-col4),"Up")=0) then do;

comment="Down";

end;

else if (index(cat(of col1-col4),"Down")=0) and (index(cat(of col1-col4),"Up")>=1) then do;

comment="Up";

end;

run;

Thank you.

Ying  

[ChrisNZ]

This should help:

data T;
  retain A1 A2 1 ;
  array A[*] A:;
  B= catt(of A[*]);
run;

B=11