Help using Base SAS procedures

from monthly dataset to quarterly

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

from monthly dataset to quarterly

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.


Accepted Solutions
Solution
‎02-16-2012 11:20 AM
Super User
Posts: 10,500

Re: from monthly dataset to quarterly

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;

View solution in original post


All Replies
Respected Advisor
Posts: 3,124

Re: from monthly dataset to quarterly

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.

Contributor
Posts: 44

Re: from monthly dataset to quarterly

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!

Respected Advisor
Posts: 3,124

Re: from monthly dataset to quarterly

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

Solution
‎02-16-2012 11:20 AM
Super User
Posts: 10,500

Re: from monthly dataset to quarterly

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;

Contributor
Posts: 44

Re: from monthly dataset to quarterly

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?

Super User
Posts: 17,829

Re: from monthly dataset to quarterly

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

Contributor
Posts: 44

Re: from monthly dataset to quarterly

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;

??

PROC Star
Posts: 7,363

Re: from monthly dataset to quarterly

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;

Contributor
Posts: 44

Re: from monthly dataset to quarterly

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?

PROC Star
Posts: 7,363

Re: from monthly dataset to quarterly

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;

Contributor
Posts: 44

Re: from monthly dataset to quarterly

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

Contributor
Posts: 53

Re: from monthly dataset to quarterly

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.

Super User
Posts: 9,681

Re: from monthly dataset to quarterly

Did you try the method proposed by ballardw ?

Ksharp

Contributor
Posts: 44

Re: from monthly dataset to quarterly

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 23 replies
  • 1383 views
  • 6 likes
  • 8 in conversation