It was fast. I've pasted the log below so you can see the time for the whole process. But, you should know I ran this on a MacBook Pro running SAS via Virtual Machine. Had I booted up into Windows directly (my hard drive is partitioned), I imagine it would have been faster still. 1818 1819 1820 data std_vertical; 1821 set StdData; 1822 array sch(*) schcode02-schcode10; 1823 do i = 1 to 9; 1824 schcode=sch(i); 1825 year=2001+i; 1826 if not missing(schcode) then output; 1827 end; 1828 drop schcode02-schcode10 i; 1829 run; NOTE: There were 139909 observations read from the data set WORK.STDDATA. NOTE: The data set WORK.STD_VERTICAL has 810540 observations and 3 variables. NOTE: Compressing data set WORK.STD_VERTICAL increased size by 27.96 percent. Compressed is 6174 pages; un-compressed would require 4825 pages. NOTE: DATA statement used (Total process time): real time 0.93 seconds user cpu time 0.29 seconds system cpu time 0.32 seconds memory 346.03k OS Memory 18620.00k Timestamp 01/21/2013 11:31:01 PM 1830 1831 proc sort data=std_vertical; 1832 by schcode year; 1833 run; NOTE: There were 810540 observations read from the data set WORK.STD_VERTICAL. NOTE: The data set WORK.STD_VERTICAL has 810540 observations and 3 variables. NOTE: Compressing data set WORK.STD_VERTICAL increased size by 27.96 percent. Compressed is 6174 pages; un-compressed would require 4825 pages. NOTE: PROCEDURE SORT used (Total process time): real time 1.48 seconds user cpu time 0.99 seconds system cpu time 0.43 seconds memory 45408.96k OS Memory 64700.00k Timestamp 01/21/2013 11:31:03 PM 1834 1835 data sch_vertical (keep=schcode 1836 year 1837 PctFRlunch 1838 PctRespParEd 1839 AvgParEd 1840 SCI 1841 TotEnr 1842 AfrAmerPct 1843 HispPct 1844 WhitePct 1845 PctTotELs 1846 PctELsSpan 1847 PctELsELDorSDAIE 1848 PctELsPLS 1849 PctELsL1 1850 SpanTstoSsRatio 1851 SpanAidestoSsRatio 1852 SpanTsandAidestoSsRatio 1853 EngTstoSsRatio 1854 PctTotReclassYr); 1855 set SchData; 1856 array PctFRlunc(*) PctFRlunch_02-PctFRlunch_10; /*-PctFRlunch_10*/; 1857 array PctRespParE(*) PctRespParEd_02-PctRespParEd_10; /*-PctRespParEd_10*/; 1858 array AvgParE(*) AvgParEd_02-AvgParEd_10; /*-AvgParEd_10*/; 1859 array SC(*) SCI_02-SCI_10; /*-SCI_10*/; 1860 array TotEn(*) TotEnr_02-TotEnr_10; /*-TotEnr_10*/; 1861 array AfrAmerPc(*) AfrAmerPct_02-AfrAmerPct_10; /*-AfrAmerPct_10*/; 1862 array HispPc(*) HispPct_02-HispPct_10; /*-HispPct_10*/; 1863 array WhitePc(*) WhitePct_02-WhitePct_10; /*-WhitePct_10*/; 1864 array PctTotEL(*) PctTotELs_02-PctTotELs_10; /*-PctTotELs_10*/; 1865 array PctELsSpa(*) PctELsSpan_02-PctELsSpan_10; /*-PctELsSpan_10*/; 1866 array PctELsELDorSDAI(*) PctELsELDorSDAIE_02-PctELsELDorSDAIE_10; /*-PctELsELDorSDAIE_10*/; 1867 array PctELsPL(*) PctELsPLS_02-PctELsPLS_10; /*-PctELsPLS_10*/; 1868 array PctELsL(*) PctELsL1_02-PctELsL1_10; /*-PctELsL1_10*/; 1869 array SpanTstoSsRati(*) SpanTstoSsRatio_02-SpanTstoSsRatio_10; /*-SpanTstoSsRatio_10*/; 1870 array SpanAidestoSsRati(*) SpanAidestoSsRatio_02-SpanAidestoSsRatio_10; 1870 ! /*-SpanAidestoSsRatio_10*/; 1871 array SpanTsandAidestoSsRati(*) SpanTsandAidestoSsRatio_02-SpanTsandAidestoSsRatio_10; 1871 ! /*-SpanTsandAidestoSsRatio_10*/; 1872 array EngTstoSsRati(*) EngTstoSsRatio_02-EngTstoSsRatio_10; /*-EngTstoSsRatio_10*/; 1873 array PctTotReclassY(*) PctTotReclassYr_02-PctTotReclassYr_10; /*-PctTotReclassYr_10*/; 1874 do i = 1 to 9; /*9*/ 1875 PctFRlunch=PctFRlunc(i); 1876 PctRespParEd=PctRespParE(i); 1877 AvgParEd=AvgParE(i); 1878 SCI=SC(i); 1879 TotEnr=TotEn(i); 1880 AfrAmerPct=AfrAmerPc(i); 1881 HispPct=HispPc(i); 1882 WhitePct=WhitePc(i); 1883 PctTotELs=PctTotEL(i); 1884 PctELsSpan=PctELsSpa(i); 1885 PctELsELDorSDAIE=PctELsELDorSDAI(i); 1886 PctELsPLS=PctELsPL(i); 1887 PctELsL1=PctELsL(i); 1888 SpanTstoSsRatio=SpanTstoSsRati(i); 1889 SpanAidestoSsRatio=SpanAidestoSsRati(i); 1890 SpanTsandAidestoSsRatio=SpanTsandAidestoSsRati(i); 1891 EngTstoSsRatio=EngTstoSsRati(i); 1892 PctTotReclassYr=PctTotReclassY(i); 1893 year=2001+i; 1894 output; 1895 end; 1896 run; NOTE: There were 599 observations read from the data set WORK.SCHDATA. NOTE: The data set WORK.SCH_VERTICAL has 5391 observations and 20 variables. NOTE: Compressing data set WORK.SCH_VERTICAL decreased size by 14.02 percent. Compressed is 92 pages; un-compressed would require 107 pages. NOTE: DATA statement used (Total process time): real time 0.06 seconds user cpu time 0.01 seconds system cpu time 0.04 seconds memory 718.37k OS Memory 18620.00k Timestamp 01/21/2013 11:31:03 PM 1897 1898 proc sort data=sch_vertical; 1899 by schcode year; 1900 run; NOTE: There were 5391 observations read from the data set WORK.SCH_VERTICAL. NOTE: The data set WORK.SCH_VERTICAL has 5391 observations and 20 variables. NOTE: Compressing data set WORK.SCH_VERTICAL decreased size by 14.02 percent. Compressed is 92 pages; un-compressed would require 107 pages. NOTE: PROCEDURE SORT used (Total process time): real time 0.01 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 1240.29k OS Memory 18620.00k Timestamp 01/21/2013 11:31:03 PM 1901 1902 data student_school; 1903 merge sch_vertical (in=a) Std_vertical (in=b); 1904 by schcode year; 1905 if a and b; 1906 run; NOTE: There were 5391 observations read from the data set WORK.SCH_VERTICAL. NOTE: There were 810540 observations read from the data set WORK.STD_VERTICAL. NOTE: The data set WORK.STUDENT_SCHOOL has 804830 observations and 21 variables. NOTE: Compressing data set WORK.STUDENT_SCHOOL decreased size by 12.13 percent. Compressed is 14734 pages; un-compressed would require 16768 pages. NOTE: DATA statement used (Total process time): real time 2.83 seconds user cpu time 0.67 seconds system cpu time 1.09 seconds memory 543.43k OS Memory 18620.00k Timestamp 01/21/2013 11:31:06 PM 1907 1908 proc sort data=student_school; 1909 by StdPseudoId year; 1910 run; NOTE: There were 804830 observations read from the data set WORK.STUDENT_SCHOOL. NOTE: The data set WORK.STUDENT_SCHOOL has 804830 observations and 21 variables. NOTE: Compressing data set WORK.STUDENT_SCHOOL decreased size by 12.12 percent. Compressed is 14736 pages; un-compressed would require 16768 pages. NOTE: PROCEDURE SORT used (Total process time): real time 5.08 seconds user cpu time 1.65 seconds system cpu time 1.13 seconds memory 161260.60k OS Memory 181428.00k Timestamp 01/21/2013 11:31:11 PM 1911 1912 %macro transpose(libname_in=work, 1913 libname_out=work, 1914 filename_in =have, 1915 filename_out=want, 1916 by_variable=id, 1917 vars=ind1-ind4, 1918 prefix=, 1919 idlabel=date, 1920 idlabel_format=yymon7., 1921 idlabel_prefix=, 1922 guessingrows=1000); 1923 1924 data _temp; 1925 set &libname_in..&filename_in. (obs=1 keep=&vars.); 1926 run; 1927 1928 %let vars_char=""; 1929 %let varlist_char=""; 1930 %let vars_num=""; 1931 %let varlist_num=""; 1932 1933 proc sql noprint; 1934 select name 1935 into :vars_char separated by " " 1936 from dictionary.columns 1937 where libname="WORK" and 1938 memname="_TEMP" and 1939 type="char" 1940 ; 1941 select name 1942 into :vars_num separated by " " 1943 from dictionary.columns 1944 where libname="WORK" and 1945 memname="_TEMP" and 1946 type="num" 1947 ; 1948 1949 %if &vars_char ne "" %then %do; 1950 select distinct 1951 %do i=1 %to %sysfunc(countw("&vars_char.")); 1952 %if &i. lt %sysfunc(countw("&vars_char.")) %then %do; 1953 " "||"&prefix."||strip(scan("&vars_char.",&i.))||"_"|| 1954 "&idlabel_prefix."|| 1955 put(&idlabel.,&idlabel_format)|| 1956 %end; 1957 %else %do; 1958 " "||"&prefix."||strip(scan("&vars_char.",&i.))||"_"|| 1959 "&idlabel_prefix."|| 1960 put(&idlabel.,&idlabel_format), 1961 %end; 1962 %end; 1963 &idlabel. 1964 into :varlist_char separated by " ", 1965 :junk 1966 from &libname_in..&filename_in. (obs=&guessingrows.) 1967 order by &idlabel. 1968 ; 1969 %let num_charvars=&sqlobs.; 1970 %end; 1971 1972 %if &vars_num ne "" %then %do; 1973 select distinct 1974 %do i=1 %to %sysfunc(countw("&vars_num.")); 1975 %if &i. lt %sysfunc(countw("&vars_num.")) %then %do; 1976 " "||"&prefix."||strip(scan("&vars_num.",&i.))||"_"|| 1977 "&idlabel_prefix."|| 1978 put(&idlabel.,&idlabel_format)|| 1979 %end; 1980 %else %do; 1981 " "||"&prefix."||strip(scan("&vars_num.",&i.))||"_"|| 1982 "&idlabel_prefix."|| 1983 put(&idlabel.,&idlabel_format), 1984 %end; 1985 %end; 1986 &idlabel. 1987 into :varlist_num separated by " ", 1988 :junk 1989 from &libname_in..&filename_in. (obs=&guessingrows.) 1990 order by &idlabel. 1991 ; 1992 %let num_numvars=&sqlobs.; 1993 %end; 1994 1995 create table _for_format as 1996 select distinct &idlabel. as start 1997 from &libname_in..&filename_in. (obs=&guessingrows.) 1998 order by &idlabel. 1999 ; 2000 %let num_numlabels=&sqlobs.; 2001 2002 quit; 2003 2004 data _for_format; 2005 set _for_format; 2006 retain fmtname "labelfmt" type "N"; 2007 label=_n_-1; 2008 run; 2009 2010 2011 proc format cntlin = _for_format; 2012 run ; 2013 2014 filename sascode temp; 2015 data _null_; 2016 file sascode; 2017 length var $32; 2018 put "data &libname_out..&filename_out.;"; 2019 put " set &libname_in..&filename_in.;"; 2020 put " by &by_variable.;"; 2021 %if &vars_char. ne "" %then %do; 2022 put " array want_char(*) $"; 2023 %do i=1 %to %eval(&num_numlabels.*%sysfunc(countw("&vars_char."))); 2024 var=scan("&varlist_char.",&i.); 2025 put @7 var; 2026 %end; 2027 put @5 ";"; 2028 put " array have_char(*) $ &vars_char.;"; 2029 put " retain want_char;"; 2030 put " if first.&by_variable. then call missing(of want_char(*));"; 2031 put " _nchar=put(&idlabel.,labelfmt.)*dim(have_char);"; 2032 put " do _i=1 to dim(have_char);"; 2033 put " want_char(_nchar+_i)=have_char(_i);"; 2034 put " end;"; 2035 %end; 2036 %if &vars_num. ne "" %then %do; 2037 put " array want_num(*) "; 2038 %do i=1 %to %eval(&num_numlabels.*%sysfunc(countw("&vars_num."))); 2039 var=scan("&varlist_num.",&i.); 2040 put @7 var; 2041 %end; 2042 put @5 ";"; 2043 put " array have_num(*) &vars_num.;"; 2044 put " retain want_num;"; 2045 put " if first.&by_variable. then call missing(of want_num(*));"; 2046 put " _nnum=put(&idlabel.,labelfmt.)*dim(have_num);"; 2047 put " do _i=1 to dim(have_num);"; 2048 put " want_num(_nnum+_i)=have_num(_i);"; 2049 put " end;"; 2050 %end; 2051 put " drop &idlabel. _: &vars.;"; 2052 put " if last.&by_variable. then output;"; 2053 put "run;"; 2054 run; 2055 %include sascode; 2056 %mend transpose; 2057 2058 options NOQUOTELENMAX; 2059 %transpose(filename_in =student_school, 2060 by_variable=StdPseudoId, 2061 vars=SchCode PctFRlunch--PctTotReclassYr, 2062 idlabel=year, 2063 idlabel_format=4., 2064 guessingrows=1000) NOTE: There were 1 observations read from the data set WORK.STUDENT_SCHOOL. NOTE: The data set WORK._TEMP has 1 observations and 19 variables. NOTE: Compressing data set WORK._TEMP increased size by 100.00 percent. Compressed is 2 pages; un-compressed would require 1 pages. NOTE: DATA statement used (Total process time): real time 0.10 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 349.37k OS Memory 18620.00k Timestamp 01/21/2013 11:31:11 PM NOTE: No rows were selected. NOTE: Compression was disabled for data set WORK._FOR_FORMAT because compression overhead would increase the size of the data set. NOTE: Table WORK._FOR_FORMAT created, with 9 rows and 1 columns. NOTE: PROCEDURE SQL used (Total process time): real time 0.18 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 1446.71k OS Memory 18620.00k Timestamp 01/21/2013 11:31:11 PM NOTE: There were 9 observations read from the data set WORK._FOR_FORMAT. NOTE: The data set WORK._FOR_FORMAT has 9 observations and 4 variables. NOTE: Compressing data set WORK._FOR_FORMAT increased size by 100.00 percent. Compressed is 2 pages; un-compressed would require 1 pages. NOTE: DATA statement used (Total process time): real time 0.01 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 302.03k OS Memory 18620.00k Timestamp 01/21/2013 11:31:11 PM NOTE: Format LABELFMT is already on the library. NOTE: Format LABELFMT has been output. NOTE: PROCEDURE FORMAT used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 169.37k OS Memory 18620.00k Timestamp 01/21/2013 11:31:11 PM NOTE: There were 9 observations read from the data set WORK._FOR_FORMAT. NOTE: 186 records were written to the file SASCODE. The minimum record length was 4. The maximum record length was 249. NOTE: DATA statement used (Total process time): real time 0.04 seconds user cpu time 0.00 seconds system cpu time 0.01 seconds memory 2035.60k OS Memory 20180.00k Timestamp 01/21/2013 11:31:11 PM NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column). 2244:11 NOTE: There were 804830 observations read from the data set WORK.STUDENT_SCHOOL. NOTE: The data set WORK.WANT has 139616 observations and 172 variables. NOTE: Compressing data set WORK.WANT decreased size by 27.67 percent. Compressed is 9181 pages; un-compressed would require 12694 pages. NOTE: DATA statement used (Total process time): real time 6.66 seconds user cpu time 0.74 seconds system cpu time 0.65 seconds memory 660.89k OS Memory 18620.00k Timestamp 01/21/2013 11:31:18 PM
... View more