Can anyone help me make one row out of the 3 rows below:
data test;
infile datalines;
input ID $10. Network $ Affl $ ;
datalines;
1598720000 HMOB A10
1598720000 CARE A11
1598720000 MAPO A12
;
proc print data=test;
run;
Output should be one row:
ID NETWORK1 AFF1 NETWORK2 AFFL2 NETWORK3 AFFL3
1598720000 HMOB A10 CARE A11 MAPO A12
If there are always three obs, this can be done in a single step:
data want;
set test;
by id notsorted;
length
Network1-Network3 $ 8
Affl1-Affl3 $ 8
;
retain Network1-Network3 Affl1-Affl3;
array n[0:2] Network1-Network3;
array a[0:2] Affl1-Affl3;
if first.Id then do;
call missing(of n[*]);
call missing(of a[*]);
end;
n[mod(_n_, 3)] = Network;
a[mod(_n_, 3)] = Affl;
if last.Id then do;
output;
end;
drop Affl Network;
run;
The variable are not in the required order, but this is just a minor change in the length-statement.
/* Assumption1 - Transposition should be done for each group of ID values */
/* Assumption2 - Dataset is sorted by ID values */
data test;
infile datalines;
input ID $10. Network $ Affl $ ;
datalines;
1598720000 HMOB A10
1598720000 CARE A11
1598720000 MAPO A12
1598720001 HMOB A10
1598720001 CARE A11
1598720001 MAPO A12
;
run;
proc transpose data=test out=trans1(drop= _name_) prefix=network;
var network;
by id;
run;
proc transpose data=test out=trans2(drop=_name_) prefix=affl;
var affl;
by id;
run;
data trans;
merge trans1 trans2;
by id;
run;
That's one way to do it using PROC TRANSPOSE. You can also use a data step.
If there are always three obs, this can be done in a single step:
data want;
set test;
by id notsorted;
length
Network1-Network3 $ 8
Affl1-Affl3 $ 8
;
retain Network1-Network3 Affl1-Affl3;
array n[0:2] Network1-Network3;
array a[0:2] Affl1-Affl3;
if first.Id then do;
call missing(of n[*]);
call missing(of a[*]);
end;
n[mod(_n_, 3)] = Network;
a[mod(_n_, 3)] = Affl;
if last.Id then do;
output;
end;
drop Affl Network;
run;
The variable are not in the required order, but this is just a minor change in the length-statement.
Merge Skill:
data test; infile datalines; input ID : $10. Network $ Affl $ ; datalines; 1598720000 HMOB A10 1598720000 CARE A11 1598720000 MAPO A12 ; data temp; set test; by id; if first.id then n=0; n+1; run; proc sql noprint nowarn; select distinct catt('temp(where=(n=',n,') rename=( Network=Network',n,' Affl=Affl',n,'))') into : merge separated by ' ' from temp order by n; quit; data want; merge &merge; by id; drop n; run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.