hi, i am trying to understand how the variable X can be converted from annual data to quarterly. (Please note that i have already the X variable in quarters but i need to understand how it has been converted)
i used Xy which is on annual basis and then using the proc expand tried to transform it to quarterly data.
However, the outcome from SAS is not what i expected. At the bottom i have attached the X variable measured in quarters (this is what i expect from SAS)
data a;
input year $4. Xy;
datalines;
2000 519859
2001 561439
2002 606166
2003 656433
2004 713004
2005 770328
2006 836010
2007 903466
2008 951003
2009 971475
;
run;
data b;
set a;
year1 = mdy(1,1,input(year,4.));
format year1 year4.;
drop year;
rename year1=year;
run;
proc expand data=b out=c from=year to=quarter;
id year;
convert xy = xq_tot / observed = total;
convert xy = xq_avg / observed = average;
run;
this is the SAS results. Compare these results with the actual (what i am expecting to see) which are at the bottom there is a difference
| Obs |
year |
xq_tot |
xq_avg |
| 1 |
2000:01:00 |
125099.3 |
504716 |
| 2 |
2000:02:00 |
127895.6 |
514664 |
| 3 |
2000:03:00 |
132067.7 |
524772 |
| 4 |
2000:04:00 |
134796.4 |
535062.9 |
| 5 |
2001:01:00 |
134477.7 |
545388.6 |
| 6 |
2001:02:00 |
138591.9 |
555823.4 |
| 7 |
2001:03:00 |
142809.7 |
566562.2 |
| 8 |
2001:04:00 |
145559.8 |
577571.8 |
| 9 |
2002:01:00 |
145118 |
588691.7 |
| 10 |
2002:02:00 |
149552.4 |
599999 |
| 11 |
2002:03:00 |
154187.9 |
611706.6 |
| 12 |
2002:04:00 |
157307.7 |
623819.9 |
| 13 |
2003:01:00 |
156997.6 |
636238.5 |
| 14 |
2003:02:00 |
161941.6 |
649124.9 |
| 15 |
2003:03:00 |
167048.3 |
662784 |
| 16 |
2003:04:00 |
170445.5 |
677066.2 |
| 17 |
2004:01:00 |
171992.8 |
691544.5 |
| 18 |
2004:02:00 |
175439.9 |
705928.8 |
| 19 |
2004:03:00 |
180946.6 |
720114.3 |
| 20 |
2004:04:00 |
184624.7 |
734118.2 |
| 21 |
2005:01:00 |
184247.7 |
748018.9 |
| 22 |
2005:02:00 |
190029.7 |
762219.8 |
| 23 |
2005:03:00 |
196016.6 |
777272.5 |
| 24 |
2005:04:00 |
200034.1 |
793227.8 |
| 25 |
2006:01:00 |
199695.3 |
809730.8 |
| 26 |
2006:02:00 |
206098.6 |
826752.3 |
| 27 |
2006:03:00 |
212817.8 |
844454.2 |
| 28 |
2006:04:00 |
217398.3 |
862430.9 |
| 29 |
2007:01:00 |
217029.6 |
879876.1 |
| 30 |
2007:02:00 |
223564.9 |
896367 |
| 31 |
2007:03:00 |
229796.5 |
911661.8 |
| 32 |
2007:04:00 |
233075 |
925369.2 |
| 33 |
2008:01:00 |
233297.7 |
937311.2 |
| 34 |
2008:02:00 |
235622.9 |
947506.6 |
| 35 |
2008:03:00 |
240203.1 |
956061.7 |
| 36 |
2008:04:00 |
241879.3 |
962945.7 |
| 37 |
2009:01:00 |
237977.5 |
968056.1 |
| 38 |
2009:02:00 |
241768.3 |
971449.5 |
| 39 |
2009:03:00 |
245424.4 |
973167.1 |
| 40 |
2009:04:00 |
246304.8 |
973152.7 |
Below is the X variable measured in quarters (this is what i am trying to replicate with SAS)
| |
X |
| 2000 Q1 |
492537 |
| 2000 Q2 |
500653 |
| 2000 Q3 |
509423 |
| 2000 Q4 |
519859 |
| 2001 Q1 |
529811 |
| 2001 Q2 |
542744 |
| 2001 Q3 |
552252 |
| 2001 Q4 |
561439 |
| 2002 Q1 |
571788 |
| 2002 Q2 |
587243 |
| 2002 Q3 |
593962 |
| 2002 Q4 |
606166 |
| 2003 Q1 |
618291 |
| 2003 Q2 |
631968 |
| 2003 Q3 |
639026 |
| 2003 Q4 |
656433 |
| 2004 Q1 |
667726 |
| 2004 Q2 |
683069 |
| 2004 Q3 |
696401 |
| 2004 Q4 |
713004 |
| 2005 Q1 |
724853 |
| 2005 Q2 |
745101 |
| 2005 Q3 |
756804 |
| 2005 Q4 |
770328 |
| 2006 Q1 |
786507 |
| 2006 Q2 |
805301 |
| 2006 Q3 |
821162 |
| 2006 Q4 |
836010 |
| 2007 Q1 |
852850 |
| 2007 Q2 |
872706 |
| 2007 Q3 |
887064 |
| 2007 Q4 |
903466 |
| 2008 Q1 |
920687 |
| 2008 Q2 |
939411 |
| 2008 Q3 |
941561 |
| 2008 Q4 |
951003 |
| 2009 Q1 |
952468 |
| 2009 Q2 |
960718 |
| 2009 Q3 |
963883 |
| 2009 Q4 |
971475 |
