BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
deega
Quartz | Level 8

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 !

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

8 REPLIES 8
Shmuel
Garnet | Level 18

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.

 

 

            

        

deega
Quartz | Level 8
@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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

 

 

 

Shmuel
Garnet | Level 18

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;
Vish33
Lapis Lazuli | Level 10

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;

Kurt_Bremser
Super User
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;
Astounding
PROC Star

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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