DATA Step, Macro, Functions and more

transposing of variables

Accepted Solution Solved
Reply
Contributor
Posts: 41
Accepted Solution

transposing of variables

 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?

Attachment

Accepted Solutions
Solution
‎05-08-2017 06:50 AM
PROC Star
Posts: 102

Re: transposing of variables

Posted in reply to alexdsa310

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

View solution in original post


All Replies
Super User
Posts: 7,845

Re: transposing of variables

Posted in reply to alexdsa310

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 763

Re: transposing of variables

Posted in reply to alexdsa310

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

Super User
Posts: 7,845

Re: transposing of variables

[ Edited ]

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 763

Re: transposing of variables

[ Edited ]
Posted in reply to KurtBremser

@KurtBremser, I stand corrected, missread this thread completely Smiley Happy

Solution
‎05-08-2017 06:50 AM
PROC Star
Posts: 102

Re: transposing of variables

Posted in reply to alexdsa310

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

Contributor
Posts: 41

Re: transposing of variables

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"

 

 

PROC Star
Posts: 102

Re: transposing of variables

Posted in reply to alexdsa310
Yes, that's what the $quote format does
Contributor
Posts: 41

Re: transposing of variables

[ Edited ]

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;

 

Super User
Super User
Posts: 7,980

Re: transposing of variables

Posted in reply to alexdsa310

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?

Contributor
Posts: 41

Re: transposing of variables

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

Super User
Super User
Posts: 7,980

Re: transposing of variables

Posted in reply to alexdsa310

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 197 views
  • 3 likes
  • 5 in conversation