topic Re: Convert data to long format using data steps in SAS Programming

Convert data to long format using data steps

Stacy — Sat, 12 Jan 2019 19:04:26 GMT

How can I convert to long format using data steps? This is the data

Data on seizure counts for 59 epileptics.


Source: Table 2 (page 664) in Thall and Vail (1990).  
With permission of Blackwell Publishing.

Reference: Thall, P.F. and Vail, S.C. (1990). Some covariance models for 
longitudinal count data with overdispersion. Biometrics, 46, 657-671,


Description:

The data are from a placebo-controlled clinical trial of 59 epileptics.
Patients with partial seizures were enrolled in a randomized
clinical trial of the anti-epileptic drug, progabide. Participants
in the study were randomized to either progabide or a placebo, as
an adjuvant to the standard anti-epileptic chemotherapy. Progabide
is an anti-epileptic drug whose primary mechanism of action is to
enhance gamma-aminobutyric acid (GABA) content; GABA is the
primary inhibitory neurotransmitter in the brain.
Prior to receiving treatment, baseline data on the number of
epileptic seizures during the preceding 8-week interval were
recorded. Counts of epileptic seizures during  2-week intervals
before each of four successive post-randomization clinic visits
were recorded.


Variable List:

Patient ID, Treatment (0=Placebo, 1=Progabide), Age, 
Baseline 8 week seizure count, First 2 week seizure count,
Second 2 week seizure count, Third 2 week seizure count,
Fourth 2 week seizure count.


           1     0      31      11      5     3     3     3
           2     0      30      11      3     5     3     3
           3     0      25       6      2     4     0     5
           4     0      36       8      4     4     1     4
           5     0      22      66      7    18     9    21
           6     0      29      27      5     2     8     7
           7     0      31      12      6     4     0     2
           8     0      36      52     40    20    23    12
           9     0      37      23      5     6     6     5
          10     0      28      10     14    13     6     0
          11     0      36      52     26    12     6    22
          12     0      24      33     12     6     8     5
          13     0      28      18      4     4     6     2
          14     0      36      42      7     9    12    14
          15     0      26      87     16    24    10     9
          16     0      26      50     11     0     0     5
          17     0      28      18      0     0     3     3
          18     0      31     111     37    29    28    29
          19     0      32      18      3     5     2     5
          20     0      21      20      3     0     6     7
          21     0      29      12      3     4     3     4
          22     0      21       9      3     4     3     4
          23     0      32      17      2     3     3     5
          24     0      25      28      8    12     2     8
          25     0      30      55     18    24    76    25
          26     0      40       9      2     1     2     1
          27     0      19      10      3     1     4     2
          28     0      22      47     13    15    13    12
          29     1      18      76     11    14     9     8
          30     1      32      38      8     7     9     4
          31     1      20      19      0     4     3     0
          32     1      20      10      3     6     1     3
          33     1      18      19      2     6     7     4
          34     1      24      24      4     3     1     3
          35     1      30      31     22    17    19    16
          36     1      35      14      5     4     7     4
          37     1      57      11      2     4     0     4
          38     1      20      67      3     7     7     7
          39     1      22      41      4    18     2     5
          40     1      28       7      2     1     1     0
          41     1      23      22      0     2     4     0
          42     1      40      13      5     4     0     3
          43     1      43      46     11    14    25    15
          44     1      21      36     10     5     3     8
          45     1      35      38     19     7     6     7
          46     1      25       7      1     1     2     4
          47     1      26      36      6    10     8     8
          48     1      25      11      2     1     0     0
          49     1      22     151    102    65    72    63
          50     1      32      22      4     3     2     4
          51     1      25      42      8     6     5     7
          52     1      35      32      1     3     1     5
          53     1      21      56     18    11    28    13
          54     1      41      24      6     3     4     0
          55     1      32      16      3     5     4     3
          56     1      26      22      1    23    19     8
          57     1      21      25      2     3     0     1
          58     1      36      13      0     0     0     0
          59     1      37      12      1     4     3     2

 My code
Data epilepsy1;
  Set sasdata.epilepsy;
  Seizure count = TSC1;
  Drop TSC1 TSC2 TSC3 TSC4;
run;
Data epilepsy2;
  Set epilepsy;
  Seizure count = TSC2;
  drop TSC1 TSC2 TSC3 TSC4;
run;
Data epilepsy3;
  Set epilepsy;
  Seizure count = TSC3;
  drop TSC1 TSC2 TSC3 TSC4;
run;
Data epilepsy4;
  Set epilepsy;
  Seizure count = TSC4;
  drop TSC1 TSC2 TSC3 TSC4;
run;
Data sasuser.epilepsylong;
  Set epilepsy1 epilepsy2 epilepsy3 epilepsy4;
run;

Re: SAS

mkeintz — Sat, 12 Jan 2019 18:48:02 GMT

@Stacy

No doubt someone can respond and give a direct answer to your question, inferring what you mean.

But I suggest this is a good opportunity to develop the habit of not only showing the data you have, but also showing what you want the data to look like. It certainly helps your correspondents and possibly helps you to resolve ambiguities in stating your objective.

Even better, if you could show your starting data in the form of a sas data step, you would be assisting us in helping youi.

Re: SAS

Reeza — Sat, 12 Jan 2019 19:03:23 GMT

Your code will likely error out, at this line in particular:

 My codeData epilepsy1;
  Set sasdata.epilepsy;
  Seizure count = TSC1;
  Drop TSC1 TSC2 TSC3 TSC4;
run;
Data epilepsy2;
  Set epilepsy;
  Seizure count = TSC2;
  drop TSC1 TSC2 TSC3 TSC4;
run;

I'm going to assume that you're trying to restructure your data from wide too long. To do this, there are several options:

1) Data Step with array

2) PROC TRANSPOSE

PROC TRANSPOSE is the easiest method to code.

Here's a tutorial on using a data step:

https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/

And using PROC TRANSPOSE:

https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/

The first example in each, is almost identical to your problem.

In the future please do not title your posts 'SAS'. It's generic and does not help when searching for solutions.

@Stacy wrote:

How can I convert to long format using data steps? This is the data

Data on seizure counts for 59 epileptics.


Source: Table 2 (page 664) in Thall and Vail (1990).  
With permission of Blackwell Publishing.

Reference: Thall, P.F. and Vail, S.C. (1990). Some covariance models for 
longitudinal count data with overdispersion. Biometrics, 46, 657-671,


Description:

The data are from a placebo-controlled clinical trial of 59 epileptics.
Patients with partial seizures were enrolled in a randomized
clinical trial of the anti-epileptic drug, progabide. Participants
in the study were randomized to either progabide or a placebo, as
an adjuvant to the standard anti-epileptic chemotherapy. Progabide
is an anti-epileptic drug whose primary mechanism of action is to
enhance gamma-aminobutyric acid (GABA) content; GABA is the
primary inhibitory neurotransmitter in the brain.
Prior to receiving treatment, baseline data on the number of
epileptic seizures during the preceding 8-week interval were
recorded. Counts of epileptic seizures during  2-week intervals
before each of four successive post-randomization clinic visits
were recorded.


Variable List:

Patient ID, Treatment (0=Placebo, 1=Progabide), Age, 
Baseline 8 week seizure count, First 2 week seizure count,
Second 2 week seizure count, Third 2 week seizure count,
Fourth 2 week seizure count.


           1     0      31      11      5     3     3     3
           2     0      30      11      3     5     3     3
           3     0      25       6      2     4     0     5
           4     0      36       8      4     4     1     4
           5     0      22      66      7    18     9    21
           6     0      29      27      5     2     8     7
           7     0      31      12      6     4     0     2
           8     0      36      52     40    20    23    12
           9     0      37      23      5     6     6     5
          10     0      28      10     14    13     6     0
          11     0      36      52     26    12     6    22
          12     0      24      33     12     6     8     5
          13     0      28      18      4     4     6     2
          14     0      36      42      7     9    12    14
          15     0      26      87     16    24    10     9
          16     0      26      50     11     0     0     5
          17     0      28      18      0     0     3     3
          18     0      31     111     37    29    28    29
          19     0      32      18      3     5     2     5
          20     0      21      20      3     0     6     7
          21     0      29      12      3     4     3     4
          22     0      21       9      3     4     3     4
          23     0      32      17      2     3     3     5
          24     0      25      28      8    12     2     8
          25     0      30      55     18    24    76    25
          26     0      40       9      2     1     2     1
          27     0      19      10      3     1     4     2
          28     0      22      47     13    15    13    12
          29     1      18      76     11    14     9     8
          30     1      32      38      8     7     9     4
          31     1      20      19      0     4     3     0
          32     1      20      10      3     6     1     3
          33     1      18      19      2     6     7     4
          34     1      24      24      4     3     1     3
          35     1      30      31     22    17    19    16
          36     1      35      14      5     4     7     4
          37     1      57      11      2     4     0     4
          38     1      20      67      3     7     7     7
          39     1      22      41      4    18     2     5
          40     1      28       7      2     1     1     0
          41     1      23      22      0     2     4     0
          42     1      40      13      5     4     0     3
          43     1      43      46     11    14    25    15
          44     1      21      36     10     5     3     8
          45     1      35      38     19     7     6     7
          46     1      25       7      1     1     2     4
          47     1      26      36      6    10     8     8
          48     1      25      11      2     1     0     0
          49     1      22     151    102    65    72    63
          50     1      32      22      4     3     2     4
          51     1      25      42      8     6     5     7
          52     1      35      32      1     3     1     5
          53     1      21      56     18    11    28    13
          54     1      41      24      6     3     4     0
          55     1      32      16      3     5     4     3
          56     1      26      22      1    23    19     8
          57     1      21      25      2     3     0     1
          58     1      36      13      0     0     0     0
          59     1      37      12      1     4     3     2

 My code
Data epilepsy1;
  Set sasdata.epilepsy;
  Seizure count = TSC1;
  Drop TSC1 TSC2 TSC3 TSC4;
run;
Data epilepsy2;
  Set epilepsy;
  Seizure count = TSC2;
  drop TSC1 TSC2 TSC3 TSC4;
run;
Data epilepsy3;
  Set epilepsy;
  Seizure count = TSC3;
  drop TSC1 TSC2 TSC3 TSC4;
run;
Data epilepsy4;
  Set epilepsy;
  Seizure count = TSC4;
  drop TSC1 TSC2 TSC3 TSC4;
run;
Data sasuser.epilepsylong;
  Set epilepsy1 epilepsy2 epilepsy3 epilepsy4;
run;

Re: Convert data to long format using data steps

PGStats — Sat, 12 Jan 2019 19:25:40 GMT

You are describing a data transposition. Use proc transpose for that:

proc transpose data=sasdata.epilepsy out=sasuser.epilepsylong
    name=interval prefix=count;
by patient_id treatment age baseline;
var TSC1 TSC2 TSC3 TSC4;
run;