topic Re: How to split a dataset into n number of tables based on variable name in SAS Procedures

How to split a dataset into n number of tables based on variable name

Programmer26 — Wed, 05 Jul 2023 20:09:58 GMT

I have a dataset with multiple columns in it. One column contains the name of the metrics and the rest of them are having summary level information for those metrics on a monthly level.
I want to split these month level information and then append the data again so thay i can have that monthly data appened vertically to the prior month rather than havin a horizontal view of the data. Unfortunately i cannot share the data here. But if any has any suggestions to give that would be highly appreciated.

Re: How to split a dataset into n number of tables based on variable name

john_mccall — Wed, 05 Jul 2023 20:35:44 GMT

Can you show sample records of what you have and what you want?

Re: How to split a dataset into n number of tables based on variable name

Tom — Wed, 05 Jul 2023 20:42:42 GMT

Sounds like you are describing a TRANSPOSE.

So assuming the data is sorted by your METRIC variable this step

proc transpose data=have out=want name=datestr ;
  by metric;
run;

Will create a new dataset by transposing all of the numeric variables.

Since you said the different variables represented different MONTHS I has it store the original name of the variable into a new variable named DATESTR.

If you want to convert that character string into an any actual date value then it will depend on how the month is recorded in the variable name.

PS Post example data. It does not have to be your REAL data, just something that is close enough that you can adjust the proposed answers to work on your real data.

Re: How to split a dataset into n number of tables based on variable name

Programmer26 — Thu, 06 Jul 2023 04:08:30 GMT

Below is the example of how my actual data looks like and how i want to represent it.
Input data
Merti name jan-23 feb-23 mar-23
A. 1. 2. 9
B. 2.2. 4. 8
C. 2.5. 5 7
D. 3. 3. 6
E. 4. 5. 7

Output data
Metric_name date. Value
A. Jan-23 1
B. Jan-23. 2.2
C. Jan-23. 2.5
D. Jan-23. 3
E. Jan-23. 4
A. Feb-23. 2
B. Feb-23. 4
C. Feb-23. 5
D. Feb-23. 3
E. Feb-23. 5

Re: How to split a dataset into n number of tables based on variable name

Programmer26 — Thu, 06 Jul 2023 04:12:46 GMT

Below is the example of how my actual data looks like and how i want to represent it.
Input data
Merti name jan-23 feb-23 mar-23
A 1 2 9
B 2.2 4 8
C 2.5 5 7
D 3 3 6
E 4 5 7

Output data
Metric_name date Value
A Jan-23 1
B Jan-23 2.2
C Jan-23 2.5
D Jan-23 3
E Jan-23 4
A Feb-23. 2
B Feb-23 4
C Feb-23 5
D Feb-23 3
E Feb-23 5

Re: How to split a dataset into n number of tables based on variable name

Tom — Thu, 06 Jul 2023 04:34:21 GMT

First let's convert your listing into an actual dataset. If those are your actual variable names (instead of just the labels used in the printing) then you will have to have the VALIDVARNAME option set to ANY to be able to use the dataset. Also number cannot have two decimal points.

options validvarname=any;
data have;
  input 'Merti name'n $  'jan-23'n 'feb-23'n 'mar-23'n ;
cards;
A. 1. 2. 9
B. 2.2 4. 8
C. 2.5 5 7
D. 3. 3. 6
E. 4. 5. 7
;

So just transpose the dataset.

proc transpose data=have out=want name=date ;
  by 'Merti name'n;
  var 'jan-23'n 'feb-23'n 'mar-23'n;
run;

Result

       Merti
Obs    name      date     COL1

  1     A.      jan-23     1.0
  2     A.      feb-23     2.0
  3     A.      mar-23     9.0
  4     B.      jan-23     2.2
  5     B.      feb-23     4.0
  6     B.      mar-23     8.0
  7     C.      jan-23     2.5
  8     C.      feb-23     5.0
  9     C.      mar-23     7.0
 10     D.      jan-23     3.0
 11     D.      feb-23     3.0
 12     D.      mar-23     6.0
 13     E.      jan-23     4.0
 14     E.      feb-23     5.0
 15     E.      mar-23     7.0

Re: How to split a dataset into n number of tables based on variable name

Kurt_Bremser — Thu, 06 Jul 2023 08:39:43 GMT

I'll make some guesses about your data:

options validvarname=any;

data have;
input metric_name $ 'jan-23'n 'feb-23'n 'mar-23'n;
datalines;
A 1 2 9
B 2.2 4 8
C 2.5 5 7
D 3 3 6
E 4 5 7
;

First, transpose to long:'

proc transpose data=have out=long (rename=(col1=value));
by metric_name;
var _numeric_;
run;

Then, convert the dates:

data want;
set long;
date = input(compress(_name_,"-),monyy5.);
format date yymmd7.;
drop _name_;
run;

Finally, sort as wanted:

proc sort data=want;
by date metric_name;
run;

Re: How to split a dataset into n number of tables based on variable name

ballardw — Tue, 11 Jul 2023 22:35:14 GMT

@Programmer26 wrote:
Below is the example of how my actual data looks like and how i want to represent it.
Input data
Merti name jan-23 feb-23 mar-23
A 1 2 9
B 2.2 4 8
C 2.5 5 7
D 3 3 6
E 4 5 7

Output data
Metric_name date Value
A Jan-23 1
B Jan-23 2.2
C Jan-23 2.5
D Jan-23 3
E Jan-23 4
A Feb-23. 2
B Feb-23 4
C Feb-23 5
D Feb-23 3
E Feb-23 5

Can be done directly reading a text file with that structure. Example:

data want;
   input metric_name $ @;
   do month=1 to 3;
      date=mdy(month,1,2023);
      input value @;
      output;
   end;
   input;
   format date Monyy7.;
   drop month;
datalines;
A 1 2 9
B 2.2 4 8
C 2.5 5 7
D 3 3 6
E 4 5 7
;

Once upon a time I inherited 30+ years worth of "data" that existed as report tables. A single file that had header information as to site, type of measurement(s) and year. Then the bulk of the "data" for a measure year was structured with 12 columns (months) and 31 rows (days). So the "trick" was to read the file starting at the right position for each piece of data and generate the appropriate date values using the column / row position.

The program above uses the @ on the first Input to hold the reading pointer on the line after reading the metric. Then since your example hat 3 months use a counter to input set a month value and the MDY function to create a date using the column counter (month) and fixed day and year along with input to read the value. The output writes each value as read. The last input without @ is to advance to the next line of the file.

I refuse to use any 2 digit years so have the MONYY7 format. If you insist on the possibly confusing 2-digit year you can use a different format.