transpose

[stancemcgraw]

Hello0-

 I'm trying to transpose a variable called gcs, because there are two values per patient. I used the following code:

proc sort data=pc.trpenmr1; by accountNum; run;

proc transpose data = pc.trpenmr1 out=pc.gcs (drop=_name_) prefix=GCS;

by accountnum;

var gcs ;

run;

I'm getting this (see attached screenshot)

I really only want it to say GCS1, GCS 2, per accountnum. How do I clean this up?

[antonbcristina]

I  don't think you posted the attachment

[Reeza]

How's is it filling in 4, 5, 6, 7 if you dont have multiples?

Please post some sample data that will replicate this issue, if you can't include your real data make some fake data that mimics your data. If you could rotate your images before posting that's helpful as well....it's hard to read data when it's sideways.

[stancemcgraw]

Ok.So you are correct there are a few values for each, but I'd like to only collect the first and last...

[stancemcgraw]

this is what it looks like prior to transpose

[Reeza]

Then you first need to use FIRST and LAST to remove keep only the records of interest. 

Then apply the PROC TRANSPOSE. 

If you want a more detailed answer, such as sample code, please provide sample data in text format. We can't write and test a program based of images unless we type in the data....

[SuryaKiran]

Hi,

You maybe missing another by variable here. 

[SuryaKiran]

Please check if this helps.

 data have;
input id num val;
datalines;
11 1 3
11 2 4
11 2 5
11 3 3
12 1 4
12 2 3
12 3 4
;
run;
proc sort data=have;
by id num;
run;
proc transpose data=have(where=(val in (3,4))) out=want(drop=_name_) prefix=col;
by id num;
id val;
var val;
run;

[art297 · Accepted solution]

Hopefully, you have another copy of the original data, because your proc sort destroyed the ordering of records in your origninal dataset. If you do have another copy, and your data are like that shown in the pdf you posted, then you could use something like the following:

libname pc '/folders/myfolders';
data pc.trpenmr1;
  input (MRN AccountNum) ($) GCS;
  cards;
RM13270173 RA0001998090 13
. . 13

RM13311641 RA0001998675 15
. . 15

RM13311782 RA000199723 14
. . 14
. . 15
;

data need (drop=_:);
  set pc.trpenmr1 (rename=(MRN=_MRN AccountNum=_AccountNUM));
  retain MRN AccountNum;
  if not missing(_MRN) then do;
    MRN=_MRN;
    AccountNum=_AccountNum;
    output;
  end;
  else if not missing(GCS)then output;
run;

proc sort data=need out=pc.trpenmr2;
  by MRN AccountNum;
run;

data pc.trpenmr2;
  set pc.trpenmr2;
  by MRN AccountNum;
  if first.MRN or last.MRN;
run;

proc transpose data = pc.trpenmr2 out=pc.gcs (drop=_name_) prefix=GCS;
  by MRN Accountnum;
  var gcs ;
run;

Art, CEO, AnalystFinder.com

[stancemcgraw]

that worked thanks!