/* We start with STACKED DATA for 2 States */ data mydata; input date $ CS ST $ State $ X1 X2 X3 ; cards; 1999Q1 1 GA Georgia 105583627 25911747 129511 1999Q2 1 GA Georgia 113505386 27881185 123984 1999Q3 1 GA Georgia 110334745 27689223 124875 1999Q4 1 GA Georgia 105355291 27163224 123609 2000Q1 1 GA Georgia 105088519 27844989 124391 2000Q2 1 GA Georgia 10709256 27693485 131355 2000Q3 1 GA Georgia 10880283 29474226 130712 2000Q4 1 GA Georgia 87485088 29670967 132462 2001Q1 1 GA Georgia 109252343 29692405 132198 2001Q2 1 GA Georgia 11006014 2990623 132238 2001Q3 1 GA Georgia 11118555 33931638 135662 2001Q4 1 GA Georgia 113994558 33630733 136418 2002Q1 1 GA Georgia 115434218 3601969 135943 2002Q2 1 GA Georgia 119051361 34858443 137599 2002Q3 1 GA Georgia 110143028 36311051 140228 2002Q4 1 GA Georgia 107991566 36104364 140534 2003Q1 1 GA Georgia 120694105 36467565 139119 2003Q2 1 GA Georgia 123133486 38514615 13894 2003Q3 1 GA Georgia 12416869 38922445 139722 2003Q4 1 GA Georgia 128927893 3938898 141532 2004Q1 1 GA Georgia 137100574 40337295 144072 2004Q2 1 GA Georgia 135141892 40501962 145353 2004Q3 1 GA Georgia 141385442 41103634 147183 2004Q4 1 GA Georgia 140955262 40626781 147797 2005Q1 1 GA Georgia 141229921 41183 147793 2005Q2 1 GA Georgia 14513956 42811602 148645 2005Q3 1 GA Georgia 145279966 44059836 148291 2005Q4 1 GA Georgia 14990797 44212022 147284 2006Q1 1 GA Georgia 150917621 43599278 148695 2006Q2 1 GA Georgia 156354521 44027758 148331 2006Q3 1 GA Georgia 15787508 43723294 149306 2006Q4 1 GA Georgia 158458612 43321111 146779 2007Q1 1 GA Georgia 161494707 43233864 147416 2007Q2 1 GA Georgia 162983961 43963816 148105 2007Q3 1 GA Georgia 164741672 44566943 147922 2007Q4 1 GA Georgia 167916201 45158278 147922 2008Q1 1 GA Georgia 168012911 44401554 148077 2008Q2 1 GA Georgia 169316816 44728122 148191 1999Q1 2 VA Virginia 66404503 27699315 14194 1999Q2 2 VA Virginia 69959939 29567251 137275 1999Q3 2 VA Virginia 71349072 3256766 13661 1999Q4 2 VA Virginia 68833342 31450301 137274 2000Q1 2 VA Virginia 70226301 33099759 138828 2000Q2 2 VA Virginia 7251932 3376745 147651 2000Q3 2 VA Virginia 74498773 35159397 147432 2000Q4 2 VA Virginia 74269245 33897793 14911 2001Q1 2 VA Virginia 74453068 33555353 150257 2001Q2 2 VA Virginia 74257698 33355825 15101 2001Q3 2 VA Virginia 76049896 33853641 152888 2001Q4 2 VA Virginia 73417788 32614601 154315 2002Q1 2 VA Virginia 74776013 33346377 153649 2002Q2 2 VA Virginia 78012892 33645758 155915 2002Q3 2 VA Virginia 7880814 33706679 157586 2002Q4 2 VA Virginia 78020415 33674835 159043 2003Q1 2 VA Virginia 8040748 34097473 156917 2003Q2 2 VA Virginia 78552877 34598992 158867 2003Q3 2 VA Virginia 81600142 34817371 159904 2003Q4 2 VA Virginia 8279573 35787622 161804 2004Q1 2 VA Virginia 88918896 35900988 163062 2004Q2 2 VA Virginia 89762208 36383694 16464 2004Q3 2 VA Virginia 92594333 35425996 165565 2004Q4 2 VA Virginia 94224056 35673074 16752 2005Q1 2 VA Virginia 96461355 35076904 165817 2005Q2 2 VA Virginia 96739306 34662586 166409 2005Q3 2 VA Virginia 95799212 35258226 166248 2005Q4 2 VA Virginia 96522743 34686473 166074 2006Q1 2 VA Virginia 97227935 34564049 16496 2006Q2 2 VA Virginia 99723087 34691689 163538 2006Q3 2 VA Virginia 100643178 34449729 16262 2006Q4 2 VA Virginia 101321378 34003454 162681 2007Q1 2 VA Virginia 101463784 33671966 163243 2007Q2 2 VA Virginia 104586895 34427194 165849 2007Q3 2 VA Virginia 105216678 34900274 166265 2007Q4 2 VA Virginia 107347252 35448078 165393 2008Q1 2 VA Virginia 107745696 35367671 166042 2008Q2 2 VA Virginia 108847102 34819149 165666 ; %macro main1; /* We will end up with UNSTACKED DATA for 2 States */ OPTIONS NOTES SOURCE SOURCE2 MPRINT MLOGIC MERROR SYMBOLGEN; data tmp1(keep=CS ST); set mydata;run;proc sort nodup;by CS;run; data _null_; set tmp1; call symput('nfiles',_n_); run; %do i=1 %to &nfiles; data _null_; set tmp1; /* The line of code below is the key here */ if &i=_n_ then call symput('myCS',CS); if &i=_n_ then call symput('myST',trim(left(ST))); run; data tmp2; set mydata; if cs^=&mycs then delete; run; %if &i=1 %then %do; data unstacked(keep=date time);set tmp2;time=_n_;run; %end; %let dsn=tmp2; %let dsid=%sysfunc(open(&dsn)); %let cnt=(%sysfunc(attrn(&dsid,nvars))); %do j = 5 %to &cnt; %let x&j=%sysfunc(varname(&dsid,&j)); %let myvar1 = %sysfunc(trim(%sysfunc(left(&&x&j)))); %let myvar2=%str(&myST&myvar1); data new(keep=&myvar2 time); set tmp2; time=_n_; &myvar2=&&x&j; run; data unstacked; merge unstacked new ;by time; run; %end; /* end of j loop */ %let rc=%sysfunc(close(&dsid)); %end; /* end of i loop */ %mend main1; %main1; /* We start with UNSTACKED DATA for 2 States */ data mydata2; input date $ time GAX1 GAX2 GAX3 VAX1 VAX2 VAX3; cards; 1999Q1 1 1055836 259117 129511 664045 276993 14194 1999Q2 2 1135054 278812 123984 699599 295673 137275 1999Q3 3 1103347 276892 124875 713491 325677 13661 1999Q4 4 1053553 271632 123609 688333 314503 137274 2000Q1 5 1050885 27845 124391 702263 330998 138828 2000Q2 6 1070926 276935 131355 725193 337675 147651 2000Q3 7 1088028 294742 130712 744988 351594 147432 2000Q4 8 874851 29671 132462 742692 338978 14911 2001Q1 9 1092523 296924 132198 744531 335554 150257 2001Q2 10 1100601 299062 132238 742577 333558 15101 2001Q3 11 1111856 339316 135662 760499 338536 152888 2001Q4 12 1139946 336307 136418 734178 326146 154315 2002Q1 13 1154342 360197 135943 74776 333464 153649 2002Q2 14 1190514 348584 137599 780129 336458 155915 2002Q3 15 110143 363111 140228 788081 337067 157586 2002Q4 16 1079916 361044 140534 780204 336748 159043 2003Q1 17 1206941 364676 139119 804075 340975 156917 2003Q2 18 1231335 385146 13894 785529 34599 158867 2003Q3 19 1241687 389224 139722 816001 348174 159904 2003Q4 20 1289279 39389 141532 827957 357876 161804 2004Q1 21 1371006 403373 144072 889189 35901 163062 2004Q2 22 1351419 40502 145353 897622 363837 16464 2004Q3 23 1413854 411036 147183 925943 35426 165565 2004Q4 24 1409553 406268 147797 942241 356731 16752 2005Q1 25 1412299 41183 147793 964614 350769 165817 2005Q2 26 1451396 428116 148645 967393 346626 166409 2005Q3 27 14528 440598 148291 957992 352582 166248 2005Q4 28 149908 44212 147284 965227 346865 166074 2006Q1 29 1509176 435993 148695 972279 34564 16496 2006Q2 30 1563545 440278 148331 997231 346917 163538 2006Q3 31 1578751 437233 149306 1006432 344497 16262 2006Q4 32 1584586 433211 146779 1013214 340035 162681 2007Q1 33 1614947 432339 147416 1014638 33672 163243 2007Q2 34 162984 439638 148105 1045869 344272 165849 2007Q3 35 1647417 445669 147922 1052167 349003 166265 2007Q4 36 1679162 451583 147922 1073473 354481 165393 2008Q1 37 1680129 444016 148077 1077457 353677 166042 2008Q2 38 1693168 447281 148191 1088471 348191 165666 ; run; %macro main2; /* We end up with STACKED DATA for 2 States */ OPTIONS NOTES SOURCE SOURCE2 MPRINT MLOGIC MERROR SYMBOLGEN; proc contents data=mydata2 out=ST(keep=name);run; data ST(keep=ST); set ST;if name="date" or name="time" then delete; ST=substr(name,1,2);run; proc sort nodupkey;by ST;run; data _null_; set st; call symput('nostates',trim(left(_n_))); run; %do i=1 %to &nostates; data _null_; set tmp1; if &i=_n_ then call symput('myST',trim(left(ST))); run; data _tmp_&i; set mydata2; run; %let dsn=mydata2; %let dsid=%sysfunc(open(&dsn)); %let cnt=(%sysfunc(attrn(&dsid,nvars))); %do j = 3 %to &cnt; %let x&j=%sysfunc(varname(&dsid,&j)); %LET mylength = %LENGTH(&&x&j); %LET newvname = %SUBSTR(&&x&j,3,%EVAL(&mylength-2)); %LET mystate = %SUBSTR(&&x&j,1,2); data _tmp_&i; set _tmp_&i; %if (&myst^=&mystate) %then drop &&x&j;; %if (&myst=&mystate) %then %do; ST="&mystate"; rename &&x&j=&newvname;; run; %end; %end; %if &i=1 %then %do; data stacked;retain ST;set _tmp_&i;run; %end; %if &i>1 %then %do; data stacked; retain ST; set stacked _tmp_&i; run; %end; %end; %let rc=%sysfunc(close(&dsid)); %mend main2; %main2;