topic Re: base sas in SAS Programming

Requesting help in restructuring data and report

rashmirao99 — Tue, 07 Aug 2018 16:49:56 GMT

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

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.

Re: base sas

error_prone — Tue, 07 Aug 2018 16:41:20 GMT

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.

Re: base sas

ballardw — Tue, 07 Aug 2018 16:46:37 GMT

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

Re: base sas

rashmirao99 — Tue, 07 Aug 2018 17:25:11 GMT

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.

Re: base sas

ballardw — Tue, 07 Aug 2018 17:43:56 GMT

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

Re: Requesting help in restructuring data and report

ChrisNZ — Wed, 08 Aug 2018 02:25:09 GMT

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

Re: base sas

Karen_Horton — Wed, 08 Aug 2018 16:29:33 GMT

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?

Re: Requesting help in restructuring data and report

DocSteve — Fri, 10 Aug 2018 16:29:16 GMT

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 ;