transposing of variables

[alexdsa310]

 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?

[Kurt_Bremser]

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;

[PeterClemmensen]

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

[Kurt_Bremser]

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

[PeterClemmensen]

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

[s_lassen · Accepted solution]

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

[alexdsa310]

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"

[s_lassen]

Yes, that's what the $quote format does

[alexdsa310]

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;

[RW9]

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?

[alexdsa310]

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

[RW9]

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