BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
alexdsa310
Obsidian | Level 7

 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?

1 ACCEPTED SOLUTION

Accepted Solutions
s_lassen
Meteorite | Level 14

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

11 REPLIES 11
Kurt_Bremser
Super User

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
Tourmaline | Level 20

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
Super User

@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
Tourmaline | Level 20

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

s_lassen
Meteorite | Level 14

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
Obsidian | Level 7

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
Meteorite | Level 14
Yes, that's what the $quote format does
alexdsa310
Obsidian | Level 7

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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