topic Re: change variable names (not so easy) in SAS Programming

change variable names (not so easy)

Onizuka — Wed, 10 Apr 2019 10:04:49 GMT

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

Re: change variable names (not so easy)

PaigeMiller — Wed, 10 Apr 2019 10:11:47 GMT

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

Re: change variable names (not so easy)

gamotte — Wed, 10 Apr 2019 10:12:18 GMT

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;

Re: change variable names (not so easy)

Onizuka — Wed, 10 Apr 2019 10:24:31 GMT

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

Re: change variable names (not so easy)

Onizuka — Wed, 10 Apr 2019 10:38:57 GMT

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 !!!

Re: change variable names (not so easy)

Kurt_Bremser — Wed, 10 Apr 2019 10:40:40 GMT

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.

Re: change variable names (not so easy)

Shmuel — Wed, 10 Apr 2019 11:04:30 GMT

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;

Re: change variable names (not so easy)

Onizuka — Wed, 10 Apr 2019 12:15:37 GMT

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

Re: change variable names (not so easy)

Onizuka — Wed, 10 Apr 2019 12:17:39 GMT

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

Re: change variable names (not so easy)

novinosrin — Wed, 10 Apr 2019 13:43:34 GMT


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;

Re: change variable names (not so easy)

novinosrin — Wed, 10 Apr 2019 16:43:49 GMT

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;

Re: change variable names (not so easy)

Onizuka — Thu, 11 Apr 2019 06:59:43 GMT

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

Re: change variable names (not so easy)

Onizuka — Thu, 11 Apr 2019 08:16:54 GMT

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 :

Re: change variable names (not so easy)

Kurt_Bremser — Thu, 11 Apr 2019 08:38:13 GMT

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

Re: change variable names (not so easy)

Onizuka — Fri, 12 Apr 2019 08:09:39 GMT

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 ^^)

Re: change variable names (not so easy)

Onizuka — Fri, 12 Apr 2019 06:57:56 GMT

I permit me to up the topic !

Re: change variable names (not so easy)

Kurt_Bremser — Fri, 12 Apr 2019 07:18:03 GMT

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.

Re: change variable names (not so easy)

Onizuka — Fri, 12 Apr 2019 08:10:13 GMT

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

Re: change variable names (not so easy)

Kurt_Bremser — Fri, 12 Apr 2019 08:24:29 GMT

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

Re: change variable names (not so easy)

Onizuka — Fri, 12 Apr 2019 08:43:28 GMT

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 !