topic Re: transpose dataset in SAS Programming

transpose dataset

paddyb — Sat, 11 Mar 2017 04:52:20 GMT

i have to transpose this dataset

Obs    usubjid idvar       idvarval   qnam             qval
1        100        vsspid    1254        error             intermittent connection
2        100        vsrefid     R12         bpstdt           2010-01-15T15:30
3        100        vsrefid     R12       bpstdt           2010-01-15T16:30
4        100        vsrefid L12         bpstdt            2010-01-15T15:35
5        100        vsrefid     L12         bpstdt            2010-01-15T16:35

i am doing this to transpose and getting error bpstdt occurs twice,how to fix it

Re: transpose dataset

Kurt_Bremser — Thu, 09 Mar 2017 07:14:16 GMT

I wrote this code to create your dataset from a data step:

data suppvs;
infile cards dlm=',';
input usubjid idvar $ idvarval $ qnam $ qval :$25.;
cards;
100,vsspid,1254,error,intermittent connection
100,vsrefid,R12,bpstdt,2010-01-15T15:30
100,vsrefid,R12,bpstdt,2010-01-15T16:30
100,vsrefid,L12,bpstdt,2010-01-15T15:35
100,vsrefid,L12,bpstdt,2010-01-15T16:35
;
run;

Please check if that resembles your data. Note that this is the proper way to post example data, as one can also set all attributes in a data step; from a simple listing we have to guess those.

And please post your intended result. The ERROR is genuine as it should be. You use qnam as ID, and a ID variable in a transpose must be unique for every by group.

Re: transpose dataset

RW9 — Thu, 09 Mar 2017 09:36:44 GMT

Out of interest, why? That looks like a VS domain from CDISC and that is in the correct format - long rather than wide. You will find it difficult to transpose as there are mutiple rows with the same data, so first you would need some further identifier to identify unique records.

Re: transpose dataset

Astounding — Thu, 09 Mar 2017 12:36:40 GMT

There is no quick syntax fix. You will have to display what you want the result to be, for someone to properly change the code..

Re: transpose dataset

paddyb — Sat, 11 Mar 2017 04:48:42 GMT

Re: transpose dataset

RW9 — Thu, 09 Mar 2017 19:32:29 GMT

I belive your SDTM domains are not correctly mapped then. Within the SUPPxx domain IDVAR, IDVARVAL, QNAM have to be unique. In the example you provide there is no way of uniquely identifying obs 2 from 3, and 4 from 5. So, QNAM variable holds the variable which will be created and QVAL the data to put into that variable, however you have 2 of the same variable (BPSTDT) for each IDVARVAL=R12 or L12. Something seems to be wrong in the mapping. Maybe QNAM should be BPSTDT1 and BPSTDT2 respectively for 2/3 and 4/5?

Obs    usubjid   idvar       idvarval   qnam             qval
1        100        vsspid     1254        error             intermittent connection
2        100        vsrefid     R12         bpstdt           2010-01-15T15:30
3        100        vsrefid     R12         bpstdt           2010-01-15T16:30
4        100        vsrefid     L12         bpstdt            2010-01-15T15:35
5        100        vsrefid     L12         bpstdt            2010-01-15T16:35