DATA Step, Macro, Functions and more

transpose

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

transpose

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?


Accepted Solutions
Solution
‎12-12-2017 12:30 PM
PROC Star
Posts: 8,145

Re: transpose

Posted in reply to stancemcgraw

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

 

View solution in original post


All Replies
SAS Employee
Posts: 24

Re: transpose

Posted in reply to stancemcgraw

I  don't think you posted the attachment

Contributor
Posts: 31

Re: transpose

Posted in reply to stancemcgraw
Super User
Posts: 23,244

Re: transpose

Posted in reply to stancemcgraw

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.

 

Contributor
Posts: 31

Re: transpose

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

Re: transpose

this is what it looks like prior to transpose

Super User
Posts: 23,244

Re: transpose

Posted in reply to stancemcgraw

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

Valued Guide
Posts: 558

Re: transpose

Posted in reply to stancemcgraw

Hi,

 

You maybe missing another by variable here. 

Thanks,
Suryakiran
Valued Guide
Posts: 558

Re: transpose

Posted in reply to stancemcgraw

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;
Thanks,
Suryakiran
Solution
‎12-12-2017 12:30 PM
PROC Star
Posts: 8,145

Re: transpose

Posted in reply to stancemcgraw

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

 

Contributor
Posts: 31

Re: transpose

that worked thanks!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 517 views
  • 0 likes
  • 5 in conversation