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 !
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;
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.
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;
Supply your example data in a data step for easy creation. I will then adapt my code to fit your needs.
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;
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.