Hi All,
My following PROC TRANSPOSE codes are working, but I want:
1. Maximum three columns and if there are more than three variables I want it in the second line.
2. Any additional space should be filled up with 'NOTUSED'.
Can somebody help please.
Thanks,
data have;
input patient_ID:$3. Visit_code $ 6.;
cards;
101 LP28M72
101 LP23M66
101 LP22M64
101 LP29M64
102 SR66F76
102 SR62F76
102 SR61F76
102 SR69F76
102 SR77F76
103 JH23F56
103 JH43F56
;
run;
proc transpose data=work.have out=work.want (drop=_name_) prefix=Var_;
by patient_ID;
var Visit_code;
run;
Expected output:
patient_ID Var_1 Var_2 Var_3
101 LP28M72 LP23M66 LP22M64
101 LP29M64 NOTUSED NOTUSED
102 SR66F76 SR62F76 SR61F76
102 SR69F76 SR77F76 NOTUSED
103 JH23F56 JH43F56 NOTUSED
Single data step version which accomodates missing visit_codes
data want;
do i = 0 by 1 until(last.patient_id and mod(i,3) = 2);
if not (i and last.patient_id) then do;
set have; by patient_id;
array var_{0:2} $8;
var_{mod(i,3)} = visit_code;
end;
else call missing(var_{mod(i,3)});
if missing(var_{mod(i,3)}) then var_{mod(i,3)} = "NOTUSED";
if mod(i,3) = 2 then output;
end;
drop i visit_code;
run;
proc print noobs; run;
1. Create a line number and increment when it's pass 3 to control the lines
2. Transpose
3. Loop over using an array and set to missing.
data have;
input patient_ID:$3. Visit_code $ 6.;
cards;
101 LP28M72
101 LP23M66
101 LP22M64
101 LP29M64
102 SR66F76
102 SR62F76
102 SR61F76
102 SR69F76
102 SR77F76
103 JH23F56
103 JH43F56
;
run;
data have2;
set have;
by patient_id;
if first.id then count=1;
else count+1;
line=floor((count-0.5)/3);
drop count;
run;
proc transpose data=work.have2 out=work.want (drop=_name_) prefix=Var_;
by patient_ID line;
var Visit_code;
run;
data want2;
set want;
array var(3) var_1-var_3;
do i=1 to dim(var);
if missing(var(i)) then var(i)="No Status";
end;
run;
proc print;run;
Change the First.ID to the correct variable, First.Patient_ID
Well, you can't do the job without a Data step (I don't think), so forget about the Proc Transpose and do the whole thing in 1 step.
Something like this works...
data want (drop= i visit_code Visit);
set have;
by patient_id;
array v(3) $7 var_1 - var_3;
retain visit 0 var_1-var_3 '';
if first.patient_id then
visit=0;
visit+1;
if visit>3 then visit=1;
v{visit} = visit_code;
if last.patient_id
or mod(visit,3) = 0 then
do;
do i=visit+1 to 3;
v{i} = 'NOTUSED';
end;
output;
end;
run;
Same idea as @Reeza, 1 less step
data have;
input patient_ID:$3. Visit_code $ 6.;
cards;
101 LP28M72
101 LP23M66
101 LP22M64
101 LP29M64
102 SR66F76
102 SR62F76
102 SR61F76
102 SR69F76
102 SR77F76
103 JH23F56
103 JH43F56
;
data have2;
do i = 1 by 1 until(last.patient_id and mod(i,3) = 0);
line = floor((i-1)/3);
if i > 1 and last.patient_id then Visit_code = "NOTUSED";
else do;
set have; by patient_id;
end;
output;
end;
drop i;
run;
proc transpose data=have2 out=want(drop=_name_ line) prefix=var_;
by patient_id line;
var visit_code;
run;
proc print noobs; run;
That is pretty easy to do with an ARRAY and a DOW loop.
data want ;
do _n_=1 by 1 until (last.pid or mod(_n_,3)=0) ;
set have ;
by pid;
array col (3) $8 ;
col(_n_)=visit;
end;
drop visit ;
run;
If you really want to have the missing values replaced with 'NOT USED' then add another DO loop before the RUN statement.
do _n_=_n_+1 to 3;
col(_n_)='NOT USED';
end;
Single data step version which accomodates missing visit_codes
data want;
do i = 0 by 1 until(last.patient_id and mod(i,3) = 2);
if not (i and last.patient_id) then do;
set have; by patient_id;
array var_{0:2} $8;
var_{mod(i,3)} = visit_code;
end;
else call missing(var_{mod(i,3)});
if missing(var_{mod(i,3)}) then var_{mod(i,3)} = "NOTUSED";
if mod(i,3) = 2 then output;
end;
drop i visit_code;
run;
proc print noobs; run;
Why use proc transpose?
data HAVE;
input PATIENT_ID:$3. VISIT_CODE $ 7.;
cards;
101 LP28M72
101 LP23M66
101 LP22M64
101 LP29M64
102 SR66F76
102 SR62F76
102 SR61F76
102 SR69F76
102 SR77F76
103 JH23F56
103 JH43F56
run;
data WANT;
array VAR [3] $7;
retain VAR:;
set HAVE;
by PATIENT_ID;
keep PATIENT_ID VAR:;
if first.PATIENT_ID | VISIT_NB=3 then VISIT_NB=0;
VISIT_NB+1;
VAR[VISIT_NB]=VISIT_CODE;
if last.PATIENT_ID | VISIT_NB=3 then do;
do while (VISIT_NB < 3);
VISIT_NB+1;
VAR[VISIT_NB]='UNUSED';
end;
output;
end;
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.