topic Transposing data set in SAS Programming

Transposing data set

Dhana18 — Thu, 31 May 2018 13:23:48 GMT

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

1212 10004 NAATR N RECTUM

1212 10004 HIV N BLOOD

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

Re: Transposing data set

RW9 — Thu, 31 May 2018 13:46:18 GMT

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;

Re: Transposing data set

data_null__ — Thu, 31 May 2018 14:08:08 GMT

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