topic Re: transpose in SAS Programming

transpose

stancemcgraw — Mon, 11 Dec 2017 19:05:35 GMT

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?

Re: transpose

antonbcristina — Mon, 11 Dec 2017 19:17:21 GMT

I don't think you posted the attachment

Re: transpose

stancemcgraw — Mon, 11 Dec 2017 20:19:12 GMT

Re: transpose

Reeza — Mon, 11 Dec 2017 20:21:25 GMT

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.

Re: transpose

stancemcgraw — Mon, 11 Dec 2017 20:27:31 GMT

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

Re: transpose

SuryaKiran — Mon, 11 Dec 2017 20:27:42 GMT

Hi,

You maybe missing another by variable here.

Re: transpose

stancemcgraw — Mon, 11 Dec 2017 20:28:55 GMT

this is what it looks like prior to transpose

Re: transpose

SuryaKiran — Mon, 11 Dec 2017 20:44:58 GMT

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;

Re: transpose

Reeza — Mon, 11 Dec 2017 20:45:03 GMT

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....

Re: transpose

art297 — Mon, 11 Dec 2017 20:58:58 GMT

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

Re: transpose

stancemcgraw — Tue, 12 Dec 2017 17:29:52 GMT

that worked thanks!