Reading multiple observations in single record and group them by month

Question

I have a data set which contains four monthly observations in each row.

1Sep11 389.00 1Oct11 491.00 1Nov11 370.00 1Dec11 335.00
2Sep11 423.00 2Oct11 478.00 2Nov11 407.00 2Dec11 442.00
3Sep11 482.00 3Oct11 300.00 3Nov11 303.00 3Dec11 372.00

I need to have a data set, which would contain the months (Sep, Oct, Nov, Dec) as four columns, and the readings against each month placed on the right column. Example.

Day|Sep|Oct|Nov|Dec
1|389.00|491.00|370.00|335.00
2|423.00|478.00|407.00|442.00
3|482.00|300.00|303.00|372.00

How can I do this in SAS? I have tried the @@ option, but that only helps me to read the four readings in the row, and create one observation for each reading.

Answer 1

You original data is in categorical form. That is good!

You are asking for a transformation that changes data (month part of date) into meta data (month name as column). This means down the road you will be dealing with arrays or variable name lists.

I would recommend keeping your data in categorical form. Categorical form means you can use CLASS and BY statements for efficient processing. Use Proc TABULATE to arrange your data items for output or delivery consumption (such as ODS EXCEL).

data have;
attrib date informat=date9. format=date9.;
input date value @@;
date_again = date;
datalines;
1Sep11 389.00 1Oct11 491.00 1Nov11 370.00 1Dec11 335.00
2Sep11 423.00 2Oct11 478.00 2Nov11 407.00 2Dec11 442.00
3Sep11 482.00 3Oct11 300.00 3Nov11 303.00 3Dec11 372.00
run;

proc tabulate data=have;
  class date date_again;
  var value; 

  format date monname.;
  format date_again day.;

  table date_again='', date=''*value=''*max='' / nocellmerge;
run;

ODS LISTING output

-------------------------------------------------------------
|       | September  |  October   |  November  |  December  |
|-------+------------+------------+------------+------------|
|1      |      389.00|      491.00|      370.00|      335.00|
|-------+------------+------------+------------+------------|
|2      |      423.00|      478.00|      407.00|      442.00|
|-------+------------+------------+------------+------------|
|3      |      482.00|      300.00|      303.00|      372.00|
-------------------------------------------------------------

If you feel you must transpose the data, split out the day and month for use as by and id

data have2(keep=day month value);
attrib date informat=date9. format=date9.;
input date value @@;
day = day(date);
month = put(date,monname3.);
datalines;
1Sep11 389.00 1Oct11 491.00 1Nov11 370.00 1Dec11 335.00
2Sep11 423.00 2Oct11 478.00 2Nov11 407.00 2Dec11 442.00
3Sep11 482.00 3Oct11 300.00 3Nov11 303.00 3Dec11 372.00
run;

proc transpose data=have2 out=want2(drop=_name_);
  by day;
  var value;
  id month;
run;

You are also going to run into problems when overall date range exceeds one year, or if the raw data rows are not day in month grouped or are disordered.

Answer 2

Code:

/* Step 1: Read each line in a string*/
data raw;
input line $ 1-70;
cards;
1Sep11 389.00 1Oct11 491.00 1Nov11 370.00 1Dec11 335.00
2Sep11 423.00 2Oct11 478.00 2Nov11 407.00 2Dec11 442.00
3Sep11 482.00 3Oct11 300.00 3Nov11 303.00 3Dec11 372.00
;;;
run;
/*Step 2: Exract the individual values separated by space */
data input;
set raw;
September= input(scan(line,1,' '),date7.);
S_Value= scan(line,2,' ');
October= input(scan(line,3,' '),date7.);
O_Value= scan(line,4,' ');
November= input(scan(line,5,' '),date7.);
N_Value= scan(line,6,' ');
December= input(scan(line,7,' '),date7.);
D_Value= scan(line,8,' ');
format September October November December date7. ;
drop line;
put _ALL_;
run;

Output:

  September=01SEP11 S_Value=389.00 October=01OCT11 O_Value=491.00
    November=01NOV11 N_Value=370.00 December=01DEC11 D_Value=335.00 _ERROR_=0 _N_=1
   September=02SEP11 S_Value=423.00 October=02OCT11 O_Value=478.00
    November=02NOV11 N_Value=407.00 December=02DEC11 D_Value=442.00 _ERROR_=0 _N_=2
   September=03SEP11 S_Value=482.00 October=03OCT11 O_Value=300.00
    November=03NOV11 N_Value=303.00 December=03DEC11 D_Value=372.00 _ERROR_=0 _N_=3