topic Re: from monthly dataset to quarterly in SAS Procedures

from monthly dataset to quarterly

carbs — Thu, 16 Feb 2012 14:51:38 GMT

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.

Re: from monthly dataset to quarterly

Haikuo — Thu, 16 Feb 2012 15:02:14 GMT

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.

Re: from monthly dataset to quarterly

carbs — Thu, 16 Feb 2012 15:11:04 GMT

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!

Re: from monthly dataset to quarterly

Haikuo — Thu, 16 Feb 2012 15:36:09 GMT

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;

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

Re: from monthly dataset to quarterly

ballardw — Thu, 16 Feb 2012 16:20:02 GMT

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;

Re: from monthly dataset to quarterly

carbs — Thu, 16 Feb 2012 20:54:05 GMT

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.) ;

------

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?

Re: from monthly dataset to quarterly

Reeza — Thu, 16 Feb 2012 21:07:20 GMT

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.;

Re: from monthly dataset to quarterly

carbs — Thu, 16 Feb 2012 21:47:36 GMT

561 data pooledddd_date;

562 set pooledddd_2000;

563 tempchar = put (Date, 6.) ;

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

--------

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

565 format dat1 date9.;

------

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

566 run;

Re: from monthly dataset to quarterly

art297 — Thu, 16 Feb 2012 21:50:29 GMT

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;

Re: from monthly dataset to quarterly

carbs — Thu, 16 Feb 2012 22:35:07 GMT

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?

Re: from monthly dataset to quarterly

art297 — Thu, 16 Feb 2012 22:46:06 GMT

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;

Re: from monthly dataset to quarterly

carbs — Thu, 16 Feb 2012 22:52:38 GMT

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

Re: from monthly dataset to quarterly

VX_Xc — Thu, 16 Feb 2012 23:24:25 GMT

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.

Re: from monthly dataset to quarterly

Ksharp — Fri, 17 Feb 2012 06:47:26 GMT

Did you try the method proposed by ballardw ?

Ksharp

Re: from monthly dataset to quarterly

carbs — Fri, 17 Feb 2012 07:22:24 GMT

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).

Re: from monthly dataset to quarterly

Ksharp — Fri, 17 Feb 2012 08:36:32 GMT

you can try to use another format.

format month6. ;

Ksharp

Re: from monthly dataset to quarterly

carbs — Mon, 20 Feb 2012 13:22:57 GMT

This solved part of the problem using ballardw's formula. However it just calculates all of countries quarterly/sixmonth returns together, how can I adjust it to get results per country?

Re: from monthly dataset to quarterly

art297 — Mon, 20 Feb 2012 14:45:40 GMT

I don't recall you having mentioned Country before. If it is a field you have, just add it at the beginning of the Class statement.

Re: from monthly dataset to quarterly

Ksharp — Tue, 21 Feb 2012 08:15:35 GMT

Can you post some sample data and the output you want be?

Ksharp

from monthly dataset to quarterly

carbs — Tue, 21 Feb 2012 09:26:25 GMT

I've attached to the first post two files, forum and forum1. First of them is the format I have the data now. Forum1 is the outcome using Vx=Xc's code, which was just how I wanted the results to be... However I need two different sets also, where there is 6month data instead of quarter, and also the case where there is only yearly data for each country. Couldn't do this with that code, maybe easier using ballardw's code..