BookmarkSubscribeRSS Feed
rashmirao99
Fluorite | Level 6

Hi

I have a dataset which looks like this.

subjid   col1  col2   col3

1001     A       B        C

 

I NEED TO CREATE COLUMN COMBING ALL THE THREE COLUMN VALUES. AND THIS IS WHAT I NEED.

 

SUBJID             COL4

1001                   A

                           B

                           C

 

I NEED THIS FOR RPOC REPORT. TRIED PROC TRANSPOSE. BUT I GET THREE ROWS WHICH I DO NOT WANT.

CAN I GET A HELP ON THIS.

 

Thanks

Rashmi.

 

 

 

 

 

 

 

 

 

 

7 REPLIES 7
error_prone
Barite | Level 11

First things first: change the title so that it matches the problem you have, something like "transposing data" seems to be adequate. Using the title “base sas" in a community about base sas programming is not that, well, informative.
Second point, don't write all upcase. It reduces readability and is seen as shouting.
Posting the code you tried will, most likely, result in more useful answers, because we can explain what went wrong or at least what you have to change.

 

Edit: proc transpose seems to do exactly what you need. So, again, please post the code you tried and explain what wrong with result. Proc transpose, of course, generates one extra column, but that can easily be dropped.

rashmirao99
Fluorite | Level 6

O.k. All I want is:

 

 

This is my data:

 

subjid            aeterm                        aedecod                                                     aesev

1005          Constipation            GASTROINTESTINAL DISORDERS                Mild

 

All I want is :

 need to create another column  "AE" which should look like this:

 

Subjid                         AE

1005                    Constipation

                            GASTROINTESTINAL DISORDERS

                              Mild

 

Instead of concatenating horizontally,  I wan it vertically.

Hope this helps.

 

Rashmi.

 

 

How can I do this?

 

May be this helps to understand.

 

 

 

 

 

 

 

 

ballardw
Super User

@rashmirao99 wrote:

O.k. All I want is:

 

 

This is my data:

 

subjid            aeterm                        aedecod                                                     aesev

1005          Constipation            GASTROINTESTINAL DISORDERS                Mild

 

All I want is :

 need to create another column  "AE" which should look like this:

 

Subjid                         AE

1005                    Constipation

                            GASTROINTESTINAL DISORDERS

                              Mild

 

Instead of concatenating horizontally,  I wan it vertically.

Hope this helps.

 

Rashmi.

 

 

How can I do this?

 

May be this helps to understand.


Why? Again, HOW will you actually use the resultant variable? It would be impractical to count or model with in general. And SAS does not really use any vertical space in table views so the practicality is very suspect. You can insert a linefeed and/or carriage return character but then printing even becomes problematic much less any other use.

Karen_Horton
Obsidian | Level 7

You can use proc transpose with the data you have created. The code should look something like the following:

 

PROC TRANSPOSE data=xxx;

BY USUBJID;

VAR aeterm aedecod aesev;

RUN;

 

You say you have tried this, but what output did it give you?

ballardw
Super User

@rashmirao99 wrote:

Hi

I have a dataset which looks like this.

subjid   col1  col2   col3

1001     A       B        C

 

I NEED TO CREATE COLUMN COMBING ALL THE THREE COLUMN VALUES. AND THIS IS WHAT I NEED.

 

SUBJID             COL4

1001                   A

                           B

                           C

 

I NEED THIS FOR RPOC REPORT. TRIED PROC TRANSPOSE. BUT I GET THREE ROWS WHICH I DO NOT WANT.

CAN I GET A HELP ON THIS 


I suspect you are trying to replicate the Excel Alt-enter behavior to place values spaced vertically in a single cell. IF so please describe in great detail what you will do with the values of Col4 in the future.  

 

Most things that place multiple actual values into a single variable cause a great deal of additional work to do anything (i.e. this is often a bad idea). For instance if you ever want to count the number of A values in col4 then the first step would be to tear the variable apart in some manner.

 

I

ChrisNZ
Tourmaline | Level 20

I NEED TO CREATE COLUMN COMBING...

I NEED THIS FOR RPOC REPORT. 

 

Can you please type correctly, correct your typos, and not use uppercase? 

Spending a few seconds on proper communication is the bare minimum when asking for free help from volunteering experts.

I also is plain politeness.

 

This does what you are requesting, though I am unsure what you are requesting is what you want.

data HAVE;
input (SUBJID   COL1  COL2   COL3) ($);
cards;
1001     A       B        C
run;
data REPORT;
  set HAVE;
  by SUBJID ;
  COL4=COL1; output;
  COL4=COL2; output;
  COL4=COL3; output;
proc report data=REPORT;
 columns SUBJID COL4;
 define SUBJID /group  ;
 define COL4   /display;
run;
SUBJID COL4
1001 A
  B
  C

 

 

 

 

DocSteve
Obsidian | Level 7

If you really want to change the shape of your dataset (a la Proc Transpose) then the following code works:


data two ;
  drop aeterm aedecod aesev ;
  set one ;
  array ae_vars (3) aeterm aedecod aesev ;
  do i = 1 to 3 ;
    ae = ae_vars(i) ;
    if i > 1 then subjid = "";
    output ;
  end ;
run ;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 7 replies
  • 1172 views
  • 5 likes
  • 6 in conversation