DATA Step, Macro, Functions and more

PROC TRANSPOSE with conditions

Accepted Solution Solved
Reply
Regular Contributor
Posts: 215
Accepted Solution

PROC TRANSPOSE with conditions

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


Accepted Solutions
Solution
‎05-24-2016 11:17 AM
Respected Advisor
Posts: 4,663

Re: PROC TRANSPOSE with conditions

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

View solution in original post


All Replies
Super User
Posts: 17,963

Re: PROC TRANSPOSE with conditions

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;
 
 

Regular Contributor
Posts: 215

Re: PROC TRANSPOSE with conditions

Hi Reeza,
By group 102 does not seem right with your code. would you please modify the code. thanks.
Super User
Posts: 17,963

Re: PROC TRANSPOSE with conditions

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

Frequent Contributor
Posts: 85

Re: PROC TRANSPOSE with conditions

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;
Respected Advisor
Posts: 4,663

Re: PROC TRANSPOSE with conditions

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;
PG
Super User
Super User
Posts: 6,502

Re: PROC TRANSPOSE with conditions

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;
Solution
‎05-24-2016 11:17 AM
Respected Advisor
Posts: 4,663

Re: PROC TRANSPOSE with conditions

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;
PG
PROC Star
Posts: 1,570

Re: PROC TRANSPOSE with conditions

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 414 views
  • 11 likes
  • 6 in conversation