BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mlogan
Lapis Lazuli | Level 10

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

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

8 REPLIES 8
Reeza
Super User

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;
 
 

mlogan
Lapis Lazuli | Level 10
Hi Reeza,
By group 102 does not seem right with your code. would you please modify the code. thanks.
Reeza
Super User

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

JerryLeBreton
Pyrite | Level 9

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;
PGStats
Opal | Level 21

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
Tom
Super User Tom
Super User

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;
PGStats
Opal | Level 21

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
ChrisNZ
Tourmaline | Level 20

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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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