DATA Step, Macro, Functions and more

Transposing data set

Reply
Occasional Contributor
Posts: 11

Transposing data set

Hi,

I have this data set:

PATIENT ID   EVENT ID    TEST    RESULT     TEST SITE

1212              10002            NAATP        P        PHARYNGEAL

1212              10002            NAATR        N        RECTUM

1212              10002             HIV             N         BLOOD

1110               10003            NAATP        P          PHARYNGEAL

1110               10003            NAATP        P          PHARYNGEAL

1212              10004            NAATR        N        RECTUM

1212              10004             HIV             N         BLOOD

1110               10005            NAATP        P          PHARYNGEAL

1110               10005            NAATP        P          PHARYNGEAL

 

 

 And I want to flip this data set like this BY UNIQUE PATIENT AND HAVE THE TEST IN SEPARATE CATEGORIES

PATIENT ID   EVENT ID    NAATP     NAATR 

1212              10002            P                 N

 

 And I want to flip this data set like this BY EVENT ID

 

I NEED TO BE ABLE TO ANALYZE THE DATA BY PATIENT WHAT KIND OF TEST DONE AND THE TEST RESULT OF EACH TEST

AND I WANT TO BE ABLE TO ANALYZE BY EVENT ID  WHAT KIND OF TEST DONE AT THAT EVENT AND THE TEST RESULT OF EACH TEST

Super User
Super User
Posts: 9,441

Re: Transposing data set

It really helps when you shout your question.  As for tranposing, you can try this, post test data in the form of a dataset in future.

data want;
  set have;
  by id;
  retain naatp naatr;
  if first.id then call missing(naatp,naatr);
  else do;
    if test="NAATP" then naatp="P";
    if test="NAATR" then naatr="N";
  end;
  if last.id then output;
run;
Respected Advisor
Posts: 3,847

Re: Transposing data set


@Dhana18 wrote:

Hi,

I have this data set:

PATIENT ID   EVENT ID    TEST    RESULT     TEST SITE

1212              10002            NAATP        P        PHARYNGEAL

1212              10002            NAATR        N        RECTUM

1212              10002             HIV             N         BLOOD

1110               10003            NAATP        P          PHARYNGEAL

1110               10003            NAATP        P          PHARYNGEAL

1212              10004            NAATR        N        RECTUM

1212              10004             HIV             N         BLOOD

1110               10005            NAATP        P          PHARYNGEAL

1110               10005            NAATP        P          PHARYNGEAL

 

 

 And I want to flip this data set like this BY UNIQUE PATIENT AND HAVE THE TEST IN SEPARATE CATEGORIES

PATIENT ID   EVENT ID    NAATP     NAATR 

1212              10002            P                 N

 

 And I want to flip this data set like this BY EVENT ID

 

I NEED TO BE ABLE TO ANALYZE THE DATA BY PATIENT WHAT KIND OF TEST DONE AND THE TEST RESULT OF EACH TEST

AND I WANT TO BE ABLE TO ANALYZE BY EVENT ID  WHAT KIND OF TEST DONE AT THAT EVENT AND THE TEST RESULT OF EACH TEST


You don't mention how to handle dups.  I will let PROC TRANSPOSE do it using the PROC statement option LET.

 

data test;
   infile cards firstobs=2;
   input patid:$4. event:$5. test:$5. result:$1.;
   cards;
PATIENT ID EVENT ID TEST RESULT TEST SITE
1212 10002 NAATP P PHARYNGEAL
1212 10002 NAATR N RECTUM
1212 10002 HIV   N BLOOD
1110 10003 NAATP P PHARYNGEAL
1110 10003 NAATP P PHARYNGEAL
1212 10004 NAATR N RECTUM
1212 10004 HIV   N BLOOD
1110 10005 NAATP P PHARYNGEAL
1110 10005 NAATP P PHARYNGEAL
;;;;
   run;
proc sort;
   by patid event;
   run;
proc print;
   run;
proc transpose data=test out=test2 let;
   by patid;
   id test;
   var result;
   run;
proc print;
   run;
Ask a Question
Discussion stats
  • 2 replies
  • 62 views
  • 0 likes
  • 3 in conversation