topic Re: PROC TRANSPOSE with conditions in SAS Programming

PROC TRANSPOSE with conditions

mlogan — Mon, 23 May 2016 03:15:43 GMT

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

Re: PROC TRANSPOSE with conditions

Reeza — Mon, 23 May 2016 03:44:49 GMT

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;

Re: PROC TRANSPOSE with conditions

JerryLeBreton — Mon, 23 May 2016 04:18:56 GMT

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;

Re: PROC TRANSPOSE with conditions

PGStats — Mon, 23 May 2016 04:20:23 GMT

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;

Re: PROC TRANSPOSE with conditions

Tom — Mon, 23 May 2016 04:46:23 GMT

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;

Re: PROC TRANSPOSE with conditions

PGStats — Mon, 23 May 2016 05:10:21 GMT

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;

Re: PROC TRANSPOSE with conditions

mlogan — Mon, 23 May 2016 17:16:06 GMT

Hi Reeza,
By group 102 does not seem right with your code. would you please modify the code. thanks.

Re: PROC TRANSPOSE with conditions

Reeza — Mon, 23 May 2016 18:25:59 GMT

Change the First.ID to the correct variable, First.Patient_ID

Re: PROC TRANSPOSE with conditions

ChrisNZ — Mon, 23 May 2016 22:32:08 GMT

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;