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;
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.
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.