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

can i transpose in existing datastep rather then using proc transpose

vraj1 — Mon, 24 Jul 2017 12:55:35 GMT

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?

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

RW9 — Mon, 24 Jul 2017 13:03:44 GMT

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.

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

Astounding — Mon, 24 Jul 2017 13:12:10 GMT

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.

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

Ksharp — Mon, 24 Jul 2017 13:27:04 GMT

calling @art297

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

vraj1 — Mon, 24 Jul 2017 14:37:30 GMT

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

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

RW9 — Mon, 24 Jul 2017 14:02:29 GMT

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.

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

Tom — Mon, 24 Jul 2017 14:27:58 GMT

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

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

art297 — Mon, 24 Jul 2017 14:53:35 GMT

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

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

Reeza — Mon, 24 Jul 2017 14:57:28 GMT

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