change variable names (not so easy)

[Onizuka]

I permit me to up the topic !

[Kurt_Bremser]

This step

data want_wide;
if _n_ = 0 then set have; /* this restores all columns, including cniv8 */
merge
  have1 (keep=group level) /* this gets us "level" back */
  want_wide1
;
by group;
drop group;
run;

is designed to get all the columns back that are not involved in the double transpose process.

If you slightly change it

data want_wide;
if _n_ = 0 then set have; /* this restores all columns, including cniv8 */
merge
  have1 (drop=cniv1-cniv8)
  want_wide1
;
by group;
drop group;
run;

it should do the trick.

[Onizuka]

Hello Kurt,

Thank you for the answer. The first time I run the codes it doesn't inverse the cniv's variables. I try it again and it seems to work. I am going to add some missing values on my real table "have" to confirm if it work really !

I come back after to put as solution, thank you for you time, really appreciate your help (and of everyone)

EDIT : @Kurt_Bremser If you have the time, could you explain the programm you made ? Because I don't understand very well what is done (well, i know that it do what i want but i don't understand every step)..

More preciselly I don't understand this :

data want;
set trans;
by group;
retain i flag;
if first.group
then do;
  i = 0;
  flag = 0;
end;
if col1 ne . then flag = 1;
if flag;
i + 1;
_name_ = cats(substr(_name_,1,4),i);
drop i;
run;

proc transpose data=want out=want_wide1 (drop=_name_);
by group;
var col1;
id _name_;
run;

data want_wide;
if _n_ = 0 then set cim_hierarchie; /* this restores all columns, including cniv8 */
merge
  have1 (drop=cniv1-cniv8)
  want_wide1
;
by group;
drop group;
run;

Thank you again !

Onizuka

[Kurt_Bremser]

I have added to additional columns to illustrate how they are "brought back", and added comments:

data have ;
input LEVEL CNIV1 CNIV2 CNIV3 CNIV4 CNIV5 CNIV6 CNIV7 CNIV8 x1 x2;
cards;
5 5000 30 600 500 5 . . . 1 1
7 5000 30 600 500 5 30 4400 . 2 2
5 1 . 3 . 5 . . . 3 3
;
run;

/* add an artificial "group" that identifies each single observation */
data have1;
set have;
group = _n_;
run;

/* transpose to long data format */
proc transpose data=have1 out=trans;
by group;
var cniv:;
run;

/* reverse the order */
proc sort data=trans;
by group descending _name_;
run;

/* create new order */
data want;
set trans;
by group;
retain i flag;
if first.group
then do;
  i = 0;
  flag = 0; /* flag will be "false" as long as the "initial" missing values are encountered */
end;
if col1 ne . then flag = 1; /* start processing only after the first non-missing value is encountered */
/* from now on, missing values will not be skipped */
if flag; /* subsetting if */
/* only now, we increment and create the new future column name */
i + 1;
_name_ = cats(substr(_name_,1,4),i);
drop i flag;
run;

/* at this point, you could keep this dataset, and join it with have1 by group
   everytime you need the other values */

/* transpose back to wide format */
proc transpose data=want out=want_wide1 (drop=_name_);
by group;
var col1;
id _name_;
run;

/* get the non-transposed columns back */
data want_wide;
if _n_ = 0 then set have; /* this restores all columns in order, including cnivX that were always missing */
merge
  have1 (drop=cniv:) /* don't bring in the original values of the transposed columns */
  want_wide1
;
by group;
drop group;
run;

proc print data=have noobs;
run;

proc print data=want_wide noobs;
run;

Results:

LEVEL    CNIV1    CNIV2    CNIV3    CNIV4    CNIV5    CNIV6    CNIV7    CNIV8    x1    x2

  5       5000      30      600      500       5         .         .      .       1     1
  7       5000      30      600      500       5        30      4400      .       2     2
  5          1       .        3        .       5         .         .      .       3     3

LEVEL    CNIV1    CNIV2    CNIV3    CNIV4    CNIV5    CNIV6    CNIV7    CNIV8    x1    x2

  5          5     500      600       30      5000       .         .      .       1     1
  7       4400      30        5      500       600      30      5000      .       2     2
  5          5       .        3        .         1       .         .      .       3     3

[Onizuka]

Thank you VERY MUCH for all these precious explanations and for adding an additional example (x1 and x2) I really appreciated it !!

I understood everything !Seriously, thank you very much again !

Have a nice day !