DATA Step, Macro, Functions and more

transpose in particular format

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

transpose in particular format

I want to transpose my data in the following way

Input:

IDNeighborDistance
X1X450.1
 X440.2
 X200.2
X5X320.3
 X590.4
 X200.4
 X120.4
X3X560.1
 X430.2
 X320.2
X2X50.2
 X210.3
 X130.3
X7X40.2
 X130.3
 X210.3
 X320.4

 

Output

 

IDneighbor1neighbor2neighbor3
X1X45X44X20
X5X32X59X20
X3X56X43X32
X2X5X21X13
X7X4X13X21

 

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 !


Accepted Solutions
Solution
‎02-08-2017 09:28 PM
Super User
Posts: 5,503

Re: transpose in particular format

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;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,563

Re: transpose in particular format

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.

 

 

            

        

Contributor
Posts: 71

Re: transpose in particular format

@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
Super User
Super User
Posts: 7,952

Re: transpose in particular format

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;

 

 

 

Super User
Posts: 7,781

Re: transpose in particular format

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 1,563

Re: transpose in particular format

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;
Frequent Contributor
Posts: 117

Re: transpose in particular format

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;

Super User
Posts: 7,781

Re: transpose in particular format

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎02-08-2017 09:28 PM
Super User
Posts: 5,503

Re: transpose in particular format

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 184 views
  • 1 like
  • 6 in conversation