DATA Step, Macro, Functions and more

Requesting help in restructuring data and report

Reply
Occasional Contributor
Posts: 12

Requesting help in restructuring data and report

[ Edited ]

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.

 

 

 

 

 

 

 

 

 

 

Highlighted
Regular Contributor
Posts: 226

Re: base sas

[ Edited ]
Posted in reply to rashmirao99

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.

Occasional Contributor
Posts: 12

Re: base sas

Posted in reply to error_prone

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.

 

 

 

 

 

 

 

 

Super User
Posts: 13,868

Re: base sas

Posted in reply to rashmirao99

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

New Contributor
Posts: 2

Re: base sas

Posted in reply to rashmirao99

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?

Super User
Posts: 13,868

Re: base sas

Posted in reply to rashmirao99

@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

Super User
Posts: 2,488

Re: Requesting help in restructuring data and report

Posted in reply to rashmirao99

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

 

 

 

 

Occasional Contributor
Posts: 9

Re: Requesting help in restructuring data and report

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 ;

Ask a Question
Discussion stats
  • 7 replies
  • 155 views
  • 5 likes
  • 6 in conversation