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