change variable names (not so easy)

[Onizuka]

Hello everyone,

The is the third time i'm writting the same topic, i have a bug, my topic remove himself after an edit...

Well, i'm writting it again.....

I have 9 variables :

- level (which contain a value between 1 and 😎

- cniv1

- cniv2

- ...

- cniv8

I'm trying to change the order of the codes depend of the level of it.

Well, images are more meaningful than words..

On this first example, i'm taking a level = 5

The first board contain what i have and the second one, what i want :

I'm putting now a second example taking level = 7

It is the same, the first board = what i have

                       the second board = what i want

Thank you for the answers and i'm really sorry for the ones who read the topic for the third time.. I promise to not edit my post anymore..

Good afternoon,

Onizuka

[PaigeMiller]

First, you might want to change the title, as I don't see how this particular problem involves changing variable names. It seems more like you need to re-order the values in these 8 variables CNIV1-CNIV8.

On this first example, i'm taking a level = 5

The first board contain what i have and the second one, what i want :

I believe you are trying to say that when level is 5, then the value of CNIV5 becomes the value of CNIV1. That seems relatively clear, but why do the other values change positions as well? You need to explain the rules by which the other values have been re-ordered within CNIV2-CNIV8 (I think I understand how CNIV1 is assigned).

[Onizuka]

Thank you for your answer. You are right, i should have change the title but I confess that I'm afraid to do it for fear that the post will be deleted again...

You understood well my problem, i am a french student and i find it difficult to explain in French, so in English... haha

I receive data from differents companies which have different hierarchies.

Some companies have 4 level of hierarchy, others 7 or 8 or 3...

When i receive the file, if a compagny have only 4 levels then cniv5 .... cniv8 is empty and the hierarchies are sorted in ascending order. But the ask me to put the hierarchy in descending order.. so the "House" on cniv1..

I hope it's more clear.

Mateo

[Shmuel]

If you define an array: array cn cniv1-cniv8;

Does LEVEL mean number of non-null members in the aarray ?

Are the null members always at the end of the array?

What you want seems to be reverse order of values.

that can be done by an auxiliary array:

data want;
 set have;
       array cn cniv1-cniv8;
       array ax  ax1-ax8;
       do i=1 to dim(ax); ax(i)=.; end;

      do i=1 to level;
           ax(i) = ax(level +1 -i);
      end;
      do i=1 to level;
          cn(i) = ax(i);
      end;
run;

[gamotte · Accepted solution]

Try the following program. If you don't need the column names to be maintained, you can drop

the last data step.

data have;
input level cniv1-cniv8;
cards;
5 1 3 0 2 4 . . .
;
run;

data want;
set have;
array revcniv(*) revcniv8-revcniv1;
array cniv(*) cniv1-cniv8;

j=1;
do i=dim(cniv) to 1 by -1;
    if cniv(i) ne . then do;
        revcniv(j)=cniv(i);
	    j+1;
	end;
end;

drop cniv: i j;
run;

/* Renale columns back to cniv* */
data _NULL_;
    set want;
	length NAME $32.;
	call execute('data want; set want;');
	do while (NAME ne "NAME");
	    call vnext(NAME);
	    if upcase(NAME)=:"REV" then do;
	        call execute(cat('rename ',NAME,"=",substr(NAME,4),";"));
	    end;
	end;
	call execute('run;');
	stop;
run;

[Onizuka]

Thank you so much for your answer and for the time you take.. i really appreciate !

It seems like it works ! I just have to change the variable names, because that happen :

So i rename the variables with this code because i don't have your skill on sas and so i don't understand your data _null_ haha

data want1 ;
set want(rename = (revcniv8 = cniv1 revcniv7 = cniv2 revcniv6 = cniv3 revcniv5 = cniv4 revcniv4 = cniv5 revcniv3 = cniv6 revcniv2 = cniv7 revcniv1 = cniv8));
run ;

After lunch, I am going to look if it works perfectly for the entire data bases and if it is OK, i'm putting your solution as THE solution haha

Again, thank you very much !!!

[Onizuka]

My bad, it is working perfectly, instead of putting "revcniv8-revcniv1" i just put "revcniv1-revcniv8", thank you again !

[Kurt_Bremser]

As soon as you abandon the disadvantageous wide format, it's all simple:

data have;
input level cniv1-cniv8;
datalines;
5 5000 400 600 10 50 . . .
7 5000 30 600 500 5 30 4400 .
;
run;

proc transpose data=have out=trans (where=(col1 ne .));
by level;
var cniv:;
run;

proc sort data=trans;
by level descending _name_;
run;

data want;
set trans;
by level;
retain i;
if first.level
then i = 1;
else i + 1;
_name_ = cats(substr(_name_,1,4),i);
drop i;
run;

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

proc print data=want_wide noobs;
run;

Result:

level    cniv1    cniv2    cniv3    cniv4    cniv5    cniv6    cniv7

  5         50      10      600      400      5000       .         .
  7       4400      30        5      500       600      30      5000

Note that the always empty cniv8 has been dropped automatically.

But I'd rather stay with the "long" format of dataset wide, as it is much easier to work with.

And

"Well, images are more meaningful than words."

No.

Code is much more meaningful than pictures, see the way I presented your example data in a form that makes it extremely easy for anyone else to recreate the dataset with just a copy/paste and submit. Don't force us to type data off screenshots, instead help us to help you.

[Onizuka]

Hello,

Thank you for you answer.

I totally agree with you.

Nevertheless, I confess that I really did not have the slightest idea of ​​how to carry out this program and that's why I did not put codes in the topic ... Moreover, I could not either to express the need that I had. That's why I took the trouble to post 2 screenshots of an example on Excel. Know that if I had a code beginning to propose, I obviously would have posted ..

Just for information, i'm not working on 2 lines but 1500 lines with more than 60 variables, i took the solution of Gamotte which works but I will try to adapt your solution to my dataset which contains more than 60 columns.

Thank you again for the answer and for the next time i will try to put some codes, sorry if it disturbes you :X

Mateo

[novinosrin]

data have;
input level cniv1-cniv8;
cards;
5 5000 400 600 10 50  . . .
7 5000 30 600 500 5 30 4400 .
;
run;

data want;
set have;
array cniv(*) cniv1-cniv8;
array t(8) _temporary_ ;
call missing(of t(*));
j=1;
do i=dim(cniv) to 1 by -1;
    if cniv(i) ne . then do;
        t(j)=cniv(i);
	    j+1;
	end;
end;
call pokelong (peekclong (addrlong(t[1]), 64), addrlong(cniv[1]), 64) ;
drop i j;
run;

[novinosrin]

Sequential numbered arrays are a joy as they are easy and contiguous

data have;
input level cniv1-cniv8 ;
cards;
5 5000 400 600 10 50  . . .
7 5000 30 600 500 5 30 4400 .
;
run;



data want;
set have(rename=(cniv1-cniv8=_c1-_c8));
array c(*) cniv1-cniv8;
array cc(*) _c8-_c1;
array _t(8);
call pokelong (peekclong (addrlong(cc[1]), 64), addrlong(_t[1]), 64) ;
_f=whichn(coalesce(of _t(*)),of _t(*));
_k=(8-_f+1)*8;
call pokelong (peekclong (addrlong(_t[_f]),_k ), addrlong(c[1]), 64) ;
drop _:;
run;

[Onizuka]

Hello novinosrin, thank you for the answer and for your time ! The programm you made is not working with my datas, i think that the reason is that my variables cniv1 - cniv8 are formatted as characters and not numbers.

Onizuka

[Onizuka]

Imagine I have an input like this :

Data have ;
  input LEVEL CNIV1 CNIV2 CNIV3 CNIV4 CNIV5 CNIV6 CNIV7 CNIV8 ;
cards;
5 5000 30 600 500 5 . . .
7 5000 30 600 500 5 30 4400 .
5 1 . 3 . 5 . . .            /* 5 levels but 2 missing values */
run ;

If i want to keep the missing values only if they are between values, is it only possible ?

So to have something like this in output :

[Kurt_Bremser]

Then we need to add a little trickery to my code:

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

/* Since level has a different meaning than I originally thought, we need to create our own group column */

data have1;
set have;
group = _n_;
run;

proc transpose data=have1 out=trans;
by group;
var cniv:;
run;

proc sort data=trans;
by group descending _name_;
run;

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 have; /* this restores all columns, including cniv8 */
merge
  have1 (keep=group level) /* this gets us "level" back */
  want_wide1
;
by group;
drop group;
run;

proc print data=want_wide noobs;
run;

Result:

LEVEL    CNIV1    CNIV2    CNIV3    CNIV4    CNIV5    CNIV6    CNIV7    CNIV8

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

[Onizuka]

Hello Kurt,

Thank you for the answer !

The problem is that when I generalize the programm to all my data, all variables are empty except cniv1 - cniv8 and level... (in total, counting cniv et level variables, I have 83 variables so I have 74 variables empty ^^)