DATA Step, Macro, Functions and more

transpose dataset

Reply
Contributor
Posts: 33

transpose dataset

[ Edited ]

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





Super User
Posts: 7,763

Re: transpose dataset

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,942

Re: transpose dataset

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.

Contributor
Posts: 33

Re: transpose dataset

[ Edited ]
 
Super User
Super User
Posts: 7,942

Re: transpose dataset

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
Super User
Posts: 5,498

Re: transpose dataset

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

Ask a Question
Discussion stats
  • 5 replies
  • 189 views
  • 1 like
  • 4 in conversation