BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
stancemcgraw
Obsidian | Level 7

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?

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

10 REPLIES 10
antonbcristina
SAS Employee

I  don't think you posted the attachment

stancemcgraw
Obsidian | Level 7
 
Reeza
Super User

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
Obsidian | Level 7
Ok.So you are correct there are a few values for each, but I'd like to only collect the first and last...
stancemcgraw
Obsidian | Level 7

this is what it looks like prior to transpose

Reeza
Super User

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
Meteorite | Level 14

Hi,

 

You maybe missing another by variable here. 

Thanks,
Suryakiran
SuryaKiran
Meteorite | Level 14

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
art297
Opal | Level 21

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
Obsidian | Level 7

that worked thanks!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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