topic Transpose help needed? in SAS Programming

Transpose help needed?

asabou01 — Wed, 15 Jul 2020 00:00:58 GMT

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

Re: Transpose help needed?

ketpt42 — Wed, 15 Jul 2020 01:06:51 GMT

/* 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.

Reshaping Data Long to Wide Using the Data Step

Reshaping Data Long to Wide Using PROC TRANSPOSE

Re: Transpose help needed?

andreas_lds — Wed, 15 Jul 2020 06:27:00 GMT

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.

Re: Transpose help needed?

Ksharp — Wed, 15 Jul 2020 12:47:13 GMT

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;