from monthly dataset to quarterly

[carbs]

Hi,

I have data with monthly observations ( between 2000-01 and 2010-12) and would like to merge the monthly data to quarterly. I've got 10 variables which values should be changed adding first three months to quarter1 (e.g. 2000:1) second three to quarter2(2000:2)  and so on. Which procedure could I use here? So I would only need to sum up three rows at a time, but so that it wold got through the whole dataset. Also, it would be nice if the code could separate when a country changes, since I have panel data.

[Haikuo]

Do you have a timestamp variable for each month? what does it look like ? such as '2000-01'? You could have numerous approaches.

1. For one, you could just do a double loop using data step, one for quarter and sum it up through all of your records. given you have multiple variables of interest, you may need an additional array loop to save your coding labor.

2. For two, depending on your timestamp variable, you could introduce quarter indicator using some format (such as QTRw.), and concatenate with year to make groups. After that, you can choose many procs, such as, proc summary, proc report, proc sql to accomplish your job.

Regards,

Haikuo

Just Edited. More detail would be appreciated, preferably sample data, both have and want.

[carbs]

Yes I have, it's in form 200001, 200002 etc. Could you give me an example of either of the approaches? Thanks for your quick reply!

[Haikuo]

Since I don't have your sample data, I will make one based on my understanding of your description, hopefully it is not completely off.

data have;

infile cards;

input my yymmn6. v1 v2 ;

format my yymmd.;

yq=cats(year(my),qtr(my));

cards;

200001     1     4

200002     2     7

200003     4     9

200004     5     12

200005     7     14

200006     8     17

200007     10     19

200008     11     22

200009     13     24

200010     14     27

200011     16     29

200012     17     32

200101     19     34

200102     20     37

200103     22     39

200104     23     42

200105     25     44

200106     26     47

200107     28     49

200108     29     52

200109     31     54

200110     32     57

200111     34     59

200112     35     62

;

/*option 1*/

data want (drop=v1 v2 i);

do _n_=1 to 3 ;

   set have;

   array v(*) v1-v2;

   array q(*) q_sum1-q_sum2;

     do i=1 to dim(v);

q(i)+v(i);

end;

end;

output;

call missing (of q_sum1 q_sum2);

run;

/*option 2*/

proc sql;

create table want as

select yq, sum(v1) as q_sum1, sum(v2) as q_sum2

from have

group by yq;

quit;

I hope you can get a bit of taste of it and apply the logic to your real life data.

Good luck!

Haikuo

[ballardw · Accepted solution]

If your "Month" variables were kept as SAS date variable the grouping could be done with formats;

proc summary data=<your data set>;

     class <your year/month variable>;

     var v1 v2;

     format <your year/month variable> YYQ6.;

     output out= newdata (drop= _type_ _freq_) sum= ;

run;

[carbs]

Thanks for both of your help! First I decided to try the latter aproach by ballardw, however I got some weird results, so I started to think that maybe the date variable I have is not a SAS date variable. I used vtype to find out that i have dates in numeric form. Then I Tried to transfer my Date variable to a sas date variable using the following code, but for some reason wasn't able to.. if I solve this, maybe the code is usable after that..  I used the code below:

525  data pooledddd_date;

526  set pooledddd_2000;

527  tempchar = put (Date, 6.) ;

528  dat1 = input(tempchar, yymm6.) ;

                            ------

                            48

ERROR 48-59: The informat YYMM was not found or could not be loaded.

529  run;

This is the log info on my codings..For some reson it doesn't accept the form yymm6. even though the dates are in form 200001 etc..Any ideas?

[Reeza]

I usually add in the 'day'  as 1 to make life easier.

There are probably other ways of doing this as well.

dat1 = put(tempchar||"01", yymmdd8.) ;

format dat1 date9.;

[carbs]

561  data pooledddd_date;

562  set pooledddd_2000;

563  tempchar = put (Date, 6.) ;

564  dat1 = put(tempchar||"01", yymmdd8.) ;

                                --------

                                48

ERROR 48-59: The format $YYMMDD was not found or could not be loaded.

565  format dat1 date9.;

                 ------

                 48

ERROR 48-59: The format $DATE was not found or could not be loaded.

566  run;

??

[art297]

This method will also work:

data pooledddd_2000;

  input date;

  cards;

200001

201005

201112

;

data pooledddd_date;

  set pooledddd_2000;

  dat1 = input(put(date,6.), yymmN6.) ;

run;

[carbs]

Used your code, and got the dates to following form:

200001	14610
200002	14641
200003	14670
200004	14701
200005	14731
200006	14762
200007	14792
200008	14823
200009	14854
200010	14884
200011	14915
200012	14945
200101	14976

etc....

So it looks like its going to the right direction, however i don't understand why didn't it convert the values to dates straight?

[art297]

It did convert the numbers to dates.  You are just missing a format.  e.g., try:

data pooledddd_date;

  set pooledddd_2000;

  format dat1 yymm8.;

  dat1 = input(put(date,6.), yymmN6.) ;

run;

[carbs]

Worked like a charm! Thanks art for your patience :smileygrin: Now will start looking the original problem!

[VX_Xc]

Say you have a dataset named “have” below;

Obs	date	quantity	price	totalprice
1	200001	10	5	50
2	200002	10	5	50
3	200003	10	5	50
4	200004	10	5	50
5	200005	10	5	50
6	200006	10	5	50
7	200007	10	5	50
8	200008	10	5	50
9	200009	10	5	50
10	200010	10	5	50
11	200011	10	5	50
12	200012	10	5	50
13	200101	10	5	50
14	200102	10	5	50
15	200103	10	5	50
16	200104	10	5	50
17	200105	10	5	50
18	200106	10	5	50
19	200107	10	5	50
20	200108	10	5	50
21	200109	10	5	50
22	200110	10	5	50
23	200111	10	5	50
24	200112	10	5	50

data have1;

      set have;

      date1 = put(date,6.);

      date2 = input(date1, yymmn6.);

      format date2 monyy7.;

      quarter = qtr(date2);

      year = year(date2);

run;

proc sort data = have1;

      by year quarter;

run;

ods trace on;

proc means data = have1 sum;

      var quantity price totalprice;

      class year quarter;

      ods output summary=out1;

run;

ods trace off;

data havefinal (drop = Nobs VName_quantity VName_price VName_totalprice);

      set out1;

run;

ods html;

proc print data = havefinal noobs;

run;

ods html close;

year	quarter	quantity_Sum	price_Sum	totalprice_Sum
2000	1	30	15	150
2000	2	30	15	150
2000	3	30	15	150
2000	4	30	15	150
2001	1	30	15	150
2001	2	30	15	150
2001	3	30	15	150
2001	4	30	15	150

If you have country variable then just add country before year in PROC SORT and PROC MEANS.

[Ksharp]

Did you try the method proposed by ballardw ?

Ksharp

[carbs]

Thanks, never been easier! Also ballardw's code was correct and easy to use! Still one question, if I want to form sixmonth data instead of quarterly, how could that be applied, since there's no direct way of telling it to sas like in the case of quarter (using qtr).