topic Re: transpose in particular format in SAS Programming

transpose in particular format

deega — Wed, 08 Feb 2017 07:24:07 GMT

I want to transpose my data in the following way

Input:

ID	Neighbor	Distance
X1	X45	0.1
	X44	0.2
	X20	0.2
X5	X32	0.3
	X59	0.4
	X20	0.4
	X12	0.4
X3	X56	0.1
	X43	0.2
	X32	0.2
X2	X5	0.2
	X21	0.3
	X13	0.3
X7	X4	0.2
	X13	0.3
	X21	0.3
	X32	0.4

Output

ID	neighbor1	neighbor2	neighbor3
X1	X45	X44	X20
X5	X32	X59	X20
X3	X56	X43	X32
X2	X5	X21	X13
X7	X4	X13	X21

Since transpose requires sorting, if I do that I lose the order of neighbors. Yes, If the whole set of ID alongwith neighbors then it does not affect the process. The order that I need to maintain in neighbor from top to bottom for each ID.

Thanks in advance !

Re: transpose in particular format

Shmuel — Wed, 08 Feb 2017 07:59:43 GMT

Are there always 3 neighbours ?

Do you want to ignore distance and drop it from output ?

Does ID repeat each row or exists on first row only ?

Is your data given in a sas dataset or in external flat and what type ?

Code should be addapted to your answers.

Re: transpose in particular format

Kurt_Bremser — Wed, 08 Feb 2017 08:03:30 GMT

data want (keep=id neighbor1 neighbor2 neighbor3);
set have;
by id notsorted;
retain neighbor1 neighbor2 neighbor3 count;
array neighbors {3} neighbor1-neighbor3 $;
if first.id
then do;
  do count = 1 to dim(neighbors);
    neighbors{count} = '';
  end;
  count = 1;
end;
if count le 3
then do;
  neighbors{count} = neighbor;
  count + 1;
end;
if last.id then output;
run;

Re: transpose in particular format

deega — Wed, 08 Feb 2017 08:52:21 GMT

@Shmuel
1. Input data has 3 or more neighbors but in output I want only 3 neighbors
2. Yes, I dont want distance in output
3. ID exists only on first row, that is the main problem for me
4. data is in SAS dataset

Re: transpose in particular format

RW9 — Wed, 08 Feb 2017 09:23:46 GMT

Post test data in the form of a datastep, this helps us see your structure. It should be very simple (not tested as I am not here to type test data in for you):

data want;
  set have;
  retain neighbour1 neightbour2 neighbour3;
  if id ne "" then call missing(neightbour1,neighbour2,neighbour3);
  if neighbour1="" then neighbour1=neighbour;
  else if neighbour2="" then neighbour2=neighbour;
  else if neighbour3="" then neighbout3=neighbour;
run;

Re: transpose in particular format

Kurt_Bremser — Wed, 08 Feb 2017 09:53:19 GMT

Supply your example data in a data step for easy creation. I will then adapt my code to fit your needs.

Re: transpose in particular format

Astounding — Wed, 08 Feb 2017 13:14:13 GMT

It might be simplest to fill in the missing ID values and then transpose. For example:

data halfway_there;

set have (rename=(ID = old_id));

if old_ID > ' ' then ID = old_ID;

retain ID;

drop old_id;

run;

proc transpose data=halfway_there out=want (keep=id neighbor1-neighbor3) prefix=neighbor;

var neighbor;

by id notsorted;

run;

Re: transpose in particular format

Shmuel — Wed, 08 Feb 2017 13:15:53 GMT

I have noticed that in lines where ID seems to be missing (I made copy/paste of your test data)

is not realy missing but have sum nonvisible/nonprintable value. Check the log after running next code.

I used for test just fotrst 3 IDs.

data have;
Input ID $ neighbor $ Distance $;
datalines;
X1  X45 0.1
　   X44 0.2
　   X20 0.2
X5  X32 0.3
　   X59 0.4
　   X20 0.4
　   X12 0.4
X3  X56 0.1
　   X43 0.2
　   X32 0.2
;run;

data want;
     format ID neighbor1-neighbor3; /* assign order of variables in output */

  set have(rename=(id=ID1)) end=eof;
        length ID neighbor1-neighbor3 miss_value  $3 ;
        retain phase 0 id count neighbor1-neighbor3;
        array ng {3} $ neighbor1-neighbor3;
        keep ID neighbor1-neighbor3;
        
        miss_value = 'E38080'x; /* value exists in ID when seen as missing ??? */
        
        if phase = 0 then do;
           if missing(ID1) or ID1 = miss_value
              then delete;   /* skip unknown IDs if exist at start of dataset */
              else do;  
                   phase=1; link initiate;     
             end;
        end;
 /*DBG*/ if _N_ < 10 then put _N_= ID1= $3.  ID1= $hex6.; 
        
        if ID1= id or missing(ID1) or ID1 = miss_value then do;
           if count < 3 then do; 
               count+1;
               ng(count) = neighbor;
           end;
       end;
       else if not missing(ID1) and ID1 ne miss_value then do;
            output;
            link initiate;    
       end;
       if eof then output;
RETURN;
INITIATE:
       ID = ID1;
       do count=1 to 3;
          call missing(ng(count));
       end;
       count=1; ng(1) = neighbor;
RETURN;
RUN;

Re: transpose in particular format

Vish33 — Wed, 08 Feb 2017 14:48:03 GMT

Try this.

data have;

infile cards dlm=" ";

input ID $3. Neighbor $3. Distance @;

cards;

X1 X45 0.1

X1 X44 0.2

X1 X20 0.2

X5 X32 0.3

X5 X59 0.4

X5 X20 0.4

X5 X12 0.4

X3 X56 0.1

X3 X43 0.2

X3 X32 0.2

X2 X5 0.2

X2 X21 0.3

X2 X13 0.3

X7 X4 0.2

X7 X13 0.3

X7 X21 0.3

X7 X32 0.4

;

run;

proc transpose data=have(drop=distance) out=want(drop= _NAME_ neighbor4) prefix=neighbor ;

var neighbor;

by id notsorted;

run;