topic Re: transposing of variables in SAS Programming

transposing of variables

alexdsa310 — Mon, 08 May 2017 08:48:21 GMT

Hi,

I have a dataset (attached sampple data) and i need to transpose them like below:

LABRARY MEM col1 col2

CTRL TAN "ETCD","EPOCH","ELEMENT","DOMAIN","ARMCD" "ETCD","EPOCH","ELEMENT","DOMAIN","ARMCD"

CTRL TS

it should be transposed by labrary and mem and concatenate all col variables into one as col1 and all nami variables as col2(concatinated).

Hope my question is clear. any help?

Re: transposing of variables

Kurt_Bremser — Mon, 08 May 2017 08:56:25 GMT

Sort and use a data step:

proc sort data=incd;
by labrary mem;
run;

data want (keep=labrary mem col1 col2);
set incd;
by labrary mem;
length col1 col2 $100;
retain col1 col2;
if first.mem
then do;
  col1 = '';
  col2 = '';
end;
col1 = catx(',',col1,col);
col2 = catx(',',col2,nami);
if last.mem then output;
run;

Re: transposing of variables

PeterClemmensen — Mon, 08 May 2017 08:57:08 GMT

Post your sample data in the form of a datastep.

You can use PROC TRANSPOSE to solve this problem, examples are given in the documentation here:

http://support.sas.com/documentation/cdl/en/proc/65145/HTML/default/viewer.htm#n0go3r6yhe1zpvn1mrf6hjw6f40g.htm

Re: transposing of variables

Kurt_Bremser — Mon, 08 May 2017 09:02:20 GMT

@PeterClemmensen: the attached .sas file contains the data step with the example data. Since it's about 200 lines of data, it was prudent to use a text file attachment for posting.

Can TRANSPOSE be used to create variables with concatenated character values?

Re: transposing of variables

s_lassen — Mon, 08 May 2017 09:09:46 GMT

Alex,

I think this does what you want:

proc sort data=incd;
  by labrary mem descending col;
run;

data want;
  do until(last.mem);
    set incd;
    by labrary mem;
    length col1 col2 $200;
    call catx(',',col1,put(col,$quote20.));
    call catx(',',col2,put(nami,$quote20.));
    end;
  keep labrary mem col1 col2;
run;

I put in the "descending col" in the PROC SORT, because your example output COL1 was sorted by descending values - you can drop it if that's not a requirement.

I used the CALL CATX routines because they are much more efficient that the CATX function for cases like this.

Regards,

Søren

Re: transposing of variables

alexdsa310 — Mon, 08 May 2017 09:15:47 GMT

Thanks soren,

a quick question can i keep " " quotes for each variable in the list of col1 and col2 like

col1

"TABLE","INC","JUY","OIP"

Re: transposing of variables

s_lassen — Mon, 08 May 2017 09:21:24 GMT

Yes, that's what the $quote format does

Re: transposing of variables

PeterClemmensen — Mon, 08 May 2017 09:32:09 GMT

@Kurt_Bremser, I stand corrected, missread this thread completely 🙂

Re: transposing of variables

RW9 — Mon, 08 May 2017 09:35:40 GMT

Out of interest, what is it your doing. That looks like SDTM data your dealing with, so why would you need to get a list of variable names? You will of course already have a metadata store, called define.xml - and you would likely have some sort of company standards/CDISC standards?

Re: transposing of variables

alexdsa310 — Mon, 08 May 2017 11:44:16 GMT

HI Soren,

I am trying to add another variable in by but not with success. i also want to have grouping basis on

labrary mem orig

any suggestion.

proc sort data=incd;
  by labrary mem orig descending col;
run;

data want;
  do until(last.mem);
    set incd;
    by labrary mem orig;
    length col1 col2 $200;
    call catx(',',col1,put(col,$quote20.));
    call catx(',',col2,put(nami,$quote20.));
    end;
  keep labrary mem col1 col2;
run;

Re: transposing of variables

alexdsa310 — Mon, 08 May 2017 11:26:24 GMT

i am creating metadata library and hence need this type of output dataset. yes the data is SDTM

Re: transposing of variables

RW9 — Mon, 08 May 2017 11:34:50 GMT

As I mentioned above, CDISC metadata library is a downloadable file from their website, and shortly via a database link (SHARE I think its called). And SAS already has a metadata library, VTable/VColumn. So the question persists, why do this yourself? Also, you may find that lists aren't very helpful, try sticking with the normlised veiw of the metadata (i.e. goes down rather than across), easier to work with, e.g.:

NAME TYPE LENGTH CODELIST..