DATA Step, Macro, Functions and more

can i transpose in existing datastep rather then using proc transpose

Accepted Solution Solved
Reply
Regular Contributor
Posts: 160
Accepted Solution

can i transpose in existing datastep rather then using proc transpose

proc transpose data=adds out=trans_adds let;
by usubjid ;
id param;
var avalc;
run;

 

i am using the above step but can i make this possible so that i can transpose in data step?


Accepted Solutions
Solution
‎07-25-2017 03:26 AM
Super User
Super User
Posts: 7,039

Re: can i transpose in existing datastep rather then using proc transpose

Can you show what you want to get? Please reduce your data to just enough columns and rows to demonstrate the issue and post it into the question using the Insert SAS code icon.  So both input and output data. Make sure to include a problem case that has duplicate rows and be clear about how you want it handled.

 

You also seems to be using the wrong variable for the ID. PARAMCD looks more like a variable name than PARAM which looks like a sentence in your data.

 

If your ID variables are not enough to uniquely identify rows then you need to add another variable.  So you might add a sequence number for each distinct value of UNIQUEID, PARAM.

proc sort data=adds out=fixed_adds ;
  by usubjid paramcd;
run;
data fixed_adds ;
  by unsubjid paramcd;
  seq+1;
  if first.paramcd then seq=1;
run;

proc transpose data=fixed_adds out=trans_adds ;
by usubjid ;
id paramcd seq;
var avalc;
run;

Then you will get variables like PRIMREAS1, PRIMREAS2 and SECREAS1, SECREAS2, ...

View solution in original post


All Replies
Super User
Super User
Posts: 7,942

Re: can i transpose in existing datastep rather then using proc transpose

Yes, you can do that in a datastep.  Why would you want to do that however, when there is a procedure specifically designed to do the task as fast as possible.  

Super User
Posts: 5,498

Re: can i transpose in existing datastep rather then using proc transpose

The presence of an ID statement makes this a complex task for a DATA step.  It can be done, but requires a fair amount of macro processing ahead of time to identify the values of PARAM (which determine the names of the variables after transposing).  I agree with @RW9's conclusion ... better off using the procedure here.

Regular Contributor
Posts: 160

Re: can i transpose in existing datastep rather then using proc transpose

[ Edited ]
Posted in reply to Astounding

Reason being i have usubjid duplicates and i need all of them and it gets omited in proc transpose. attached is test data.

 

Super User
Super User
Posts: 7,942

Re: can i transpose in existing datastep rather then using proc transpose

Then you have a more fundamental problem than wether to use a transpose or not.  Core concept of CDISC models is that each record is unique within its row.  If you cannot identify a unique record from the data you need to add another category to the SDTM so that you can identify unique records, or have columns for the data that applies to the unique record of data. 

In the case you provide (and I would recommend posting test data in the form of a datastep - i.e. test data, and as a datastep in the {i} code window, just a couple of rows is fine, not actual study data!), What I think your looking for is a summation of the discontinuation reasons across a subject, which I suppose looks like a transpose.  I would do it this way:

data want (keep=usubjid reasons);
  set have;
  by usubjid;  /* assumes sorted */
  retain reasons;
  length reasons $2000;
  if first.usubjid then reasons=dsterm;
  else reasons=catx(',',reasons,dsterm);
  if last.usubjid then output;
run;

This will add each reason to the reatined variable and on the last record for a subject output, so you have one row per subject with all the reasons in one field, which can then be merged onto your other data.

Solution
‎07-25-2017 03:26 AM
Super User
Super User
Posts: 7,039

Re: can i transpose in existing datastep rather then using proc transpose

Can you show what you want to get? Please reduce your data to just enough columns and rows to demonstrate the issue and post it into the question using the Insert SAS code icon.  So both input and output data. Make sure to include a problem case that has duplicate rows and be clear about how you want it handled.

 

You also seems to be using the wrong variable for the ID. PARAMCD looks more like a variable name than PARAM which looks like a sentence in your data.

 

If your ID variables are not enough to uniquely identify rows then you need to add another variable.  So you might add a sequence number for each distinct value of UNIQUEID, PARAM.

proc sort data=adds out=fixed_adds ;
  by usubjid paramcd;
run;
data fixed_adds ;
  by unsubjid paramcd;
  seq+1;
  if first.paramcd then seq=1;
run;

proc transpose data=fixed_adds out=trans_adds ;
by usubjid ;
id paramcd seq;
var avalc;
run;

Then you will get variables like PRIMREAS1, PRIMREAS2 and SECREAS1, SECREAS2, ...

Super User
Posts: 19,770

Re: can i transpose in existing datastep rather then using proc transpose

@vraj1 There is no test data attached?

 


vraj1 wrote:

Reason being i have usubjid duplicates and i need all of them and it gets omited in proc transpose. attached is test data.

 


 

Super User
Posts: 10,020

Re: can i transpose in existing datastep rather then using proc transpose

calling @art297

PROC Star
Posts: 7,468

Re: can i transpose in existing datastep rather then using proc transpose

I agree with @Tom other than I presume you will always have a primary reason and, sometimes, secondary reason(s).

 

If so, you might be able to use something as simple as:

proc transpose data=adds out=trans_adds prefix=reasons_for_withdrawal;
  by usubjid ;
  var avalc;
run;

Art, CEO, AnalystFinder.com

 

☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 201 views
  • 0 likes
  • 7 in conversation