Thank you very much for this. Again, I really appreciate it. This code worked in that within the ‘want’ file, students are matched up with the correct, year-specific school variables. This worked very well. However, there are only 81,687 observations (students) in the ‘want’ file, and in the original ‘StdData’ file there are 139,909 students. Also, in the ‘want’ file only the school variables 2002 through 2004 are included (rather than through 2010). The error(s) could be mine. I created several more school variables and included them in the ‘SchData’ file. I modified your code to include these variables. The only other changes I made were to call the unique student identifier “StdPseudoId” rather than “StudentId” as this is how it is in the original file (I mistakenly wrote ‘StudentId’ in my posted question); and a couple of other variable names were slightly different in my file from what I wrote in the post, so I changed the code in those places as well. It does not seem that variable names are the issue, though, because for 2002, 2003, 2004 all variables are perfect in the ‘want’ file. Could you take a look at my log (below) and let me know if you understand the error? Thanks very much for your help. NOTE: The import data set has 139909 observations and 10 variables. NOTE: Compressing data set WORK.STDDATA decreased size by 17.89 percent. Compressed is 1138 pages; un-compressed would require 1386 pages. NOTE: WORK.STDDATA data set was successfully created. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.35 seconds user cpu time 0.04 seconds system cpu time 0.20 seconds memory 225.01k OS Memory 18108.00k Timestamp 01/21/2013 05:06:15 PM NOTE: PROCEDURE CONTENTS used (Total process time): real time 0.06 seconds user cpu time 0.06 seconds system cpu time 0.01 seconds memory 296.54k OS Memory 18108.00k Timestamp 01/21/2013 05:06:22 PM NOTE: The import data set has 599 observations and 170 variables. NOTE: Compressing data set WORK.SCHDATA decreased size by 17.31 percent. Compressed is 43 pages; un-compressed would require 52 pages. NOTE: WORK.SCHDATA data set was successfully created. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.05 seconds user cpu time 0.03 seconds system cpu time 0.01 seconds memory 575.53k OS Memory 18108.00k Timestamp 01/21/2013 05:06:41 PM PROC CONTENTS DATA = StdData VARNUM; run; PROC CONTENTS DATA = SchData VARNUM; run; NOTE: PROCEDURE CONTENTS used (Total process time): real time 0.16 seconds user cpu time 0.06 seconds system cpu time 0.03 seconds memory 416.18k OS Memory 18108.00k Timestamp 01/21/2013 05:06:49 PM 509 data std_vertical; 510 set StdData; 511 array sch(*) schcode02-schcode10; 512 do i = 1 to 9; 513 schcode=sch(i); 514 year=2001+i; 515 if not missing(schcode) then output; 516 end; 517 drop schcode02-schcode10 i; 518 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.91 seconds user cpu time 0.56 seconds system cpu time 0.25 seconds memory 301.56k OS Memory 18108.00k Timestamp 01/21/2013 05:07:53 PM 519 520 proc sort data=std_vertical; 521 by schcode year; 522 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 11.29 seconds user cpu time 1.88 seconds system cpu time 1.54 seconds memory 45409.18k OS Memory 64188.00k Timestamp 01/21/2013 05:08:04 PM 523 524 data sch_vertical (keep=schcode 525 year 526 PctFRlunch 527 PctRespParEd 528 AvgParEd 529 SCI 530 TotEnr 531 AfrAmerPct 532 HispPct 533 WhitePct 534 PctTotELs 535 PctELsSpan 536 PctELsELDorSDAIE 537 PctELsPLS 538 PctELsL1 539 SpanTstoSsRatio 540 SpanAidestoSsRatio 541 SpanTsandAidestoSsRatio 542 EngTstoSsRatio 543 PctTotReclassYr); 544 set SchData; 545 array PctFRlunc(*) PctFRlunch_02-PctFRlunch_04; /*-PctFRlunch_10*/; 546 array PctRespParE(*) PctRespParEd_02-PctRespParEd_04; /*-PctRespParEd_10*/; 547 array AvgParE(*) AvgParEd_02-AvgParEd_04; /*-AvgParEd_10*/; 548 array SC(*) SCI_02-SCI_04; /*-SCI_10*/; 549 array TotEn(*) TotEnr_02-TotEnr_04; /*-TotEnr_10*/; 550 array AfrAmerPc(*) AfrAmerPct_02-AfrAmerPct_04; /*-AfrAmerPct_10*/; 551 array HispPc(*) HispPct_02-HispPct_04; /*-HispPct_10*/; 552 array WhitePc(*) WhitePct_02-WhitePct_04; /*-WhitePct_10*/; 553 array PctTotEL(*) PctTotELs_02-PctTotELs_04; /*-PctTotELs_10*/; 554 array PctELsSpa(*) PctELsSpan_02-PctELsSpan_04; /*-PctELsSpan_10*/; 555 array PctELsELDorSDAI(*) PctELsELDorSDAIE_02-PctELsELDorSDAIE_04; /*-PctELsELDorSDAIE_10*/; 556 array PctELsPL(*) PctELsPLS_02-PctELsPLS_04; /*-PctELsPLS_10*/; 557 array PctELsL(*) PctELsL1_02-PctELsL1_04; /*-PctELsL1_10*/; 558 array SpanTstoSsRati(*) SpanTstoSsRatio_02-SpanTstoSsRatio_04; /*-SpanTstoSsRatio_10*/; 559 array SpanAidestoSsRati(*) SpanAidestoSsRatio_02-SpanAidestoSsRatio_04; 559! /*-SpanAidestoSsRatio_10*/; 560 array SpanTsandAidestoSsRati(*) SpanTsandAidestoSsRatio_02-SpanTsandAidestoSsRatio_04; 560! /*-SpanTsandAidestoSsRatio_10*/; 561 array EngTstoSsRati(*) EngTstoSsRatio_02-EngTstoSsRatio_04; /*-EngTstoSsRatio_10*/; 562 array PctTotReclassY(*) PctTotReclassYr_02-PctTotReclassYr_04; /*-PctTotReclassYr_10*/; 563 do i = 1 to 3; /*9*/ 564 PctFRlunch=PctFRlunc(i); 565 PctRespParEd=PctRespParE(i); 566 AvgParEd=AvgParE(i); 567 SCI=SC(i); 568 TotEnr=TotEn(i); 569 AfrAmerPct=AfrAmerPc(i); 570 HispPct=HispPc(i); 571 WhitePct=WhitePc(i); 572 PctTotELs=PctTotEL(i); 573 PctELsSpan=PctELsSpa(i); 574 PctELsELDorSDAIE=PctELsELDorSDAI(i); 575 PctELsPLS=PctELsPL(i); 576 PctELsL1=PctELsL(i); 577 SpanTstoSsRatio=SpanTstoSsRati(i); 578 SpanAidestoSsRatio=SpanAidestoSsRati(i); 579 SpanTsandAidestoSsRatio=SpanTsandAidestoSsRati(i); 580 EngTstoSsRatio=EngTstoSsRati(i); 581 PctTotReclassYr=PctTotReclassY(i); 582 year=2001+i; 583 output; 584 end; 585 run; NOTE: There were 599 observations read from the data set WORK.SCHDATA. NOTE: The data set WORK.SCH_VERTICAL has 1797 observations and 20 variables. NOTE: Compressing data set WORK.SCH_VERTICAL decreased size by 11.11 percent. Compressed is 32 pages; un-compressed would require 36 pages. NOTE: DATA statement used (Total process time): real time 0.14 seconds user cpu time 0.04 seconds system cpu time 0.07 seconds memory 606.57k OS Memory 18364.00k Timestamp 01/21/2013 05:08:04 PM 586 587 proc sort data=sch_vertical; 588 by schcode year; 589 run; NOTE: There were 1797 observations read from the data set WORK.SCH_VERTICAL. NOTE: The data set WORK.SCH_VERTICAL has 1797 observations and 20 variables. NOTE: Compressing data set WORK.SCH_VERTICAL decreased size by 11.11 percent. Compressed is 32 pages; un-compressed would require 36 pages. NOTE: PROCEDURE SORT used (Total process time): real time 0.02 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 561.84k OS Memory 18364.00k Timestamp 01/21/2013 05:08:04 PM 590 591 data student_school; 592 merge sch_vertical (in=a) Std_vertical (in=b); 593 by schcode year; 594 if a and b; 595 run; NOTE: There were 1797 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 170700 observations and 21 variables. NOTE: Compressing data set WORK.STUDENT_SCHOOL decreased size by 9.28 percent. Compressed is 3227 pages; un-compressed would require 3557 pages. NOTE: DATA statement used (Total process time): real time 0.81 seconds user cpu time 0.48 seconds system cpu time 0.28 seconds memory 543.12k OS Memory 18364.00k Timestamp 01/21/2013 05:08:05 PM 596 597 proc sort data=student_school; 598 by StdPseudoId year; 599 run; NOTE: There were 170700 observations read from the data set WORK.STUDENT_SCHOOL. NOTE: The data set WORK.STUDENT_SCHOOL has 170700 observations and 21 variables. NOTE: Compressing data set WORK.STUDENT_SCHOOL decreased size by 9.28 percent. Compressed is 3227 pages; un-compressed would require 3557 pages. NOTE: PROCEDURE SORT used (Total process time): real time 1.43 seconds user cpu time 0.51 seconds system cpu time 0.51 seconds memory 34453.26k OS Memory 51388.00k Timestamp 01/21/2013 05:08:07 PM 600 601 %macro transpose(libname_in=work, 602 libname_out=work, 603 filename_in =have, 604 filename_out=want, 605 by_variable=id, 606 vars=ind1-ind4, 607 prefix=, 608 idlabel=date, 609 idlabel_format=yymon7., 610 idlabel_prefix=, 611 guessingrows=1000); 612 613 data _temp; 614 set &libname_in..&filename_in. (obs=1 keep=&vars.); 615 run; 616 617 %let vars_char=""; 618 %let varlist_char=""; 619 %let vars_num=""; 620 %let varlist_num=""; 621 622 proc sql noprint; 623 select name 624 into :vars_char separated by " " 625 from dictionary.columns 626 where libname="WORK" and 627 memname="_TEMP" and 628 type="char" 629 ; 630 select name 631 into :vars_num separated by " " 632 from dictionary.columns 633 where libname="WORK" and 634 memname="_TEMP" and 635 type="num" 636 ; 637 638 %if &vars_char ne "" %then %do; 639 select distinct 640 %do i=1 %to %sysfunc(countw("&vars_char.")); 641 %if &i. lt %sysfunc(countw("&vars_char.")) %then %do; 642 " "||"&prefix."||strip(scan("&vars_char.",&i.))||"_"|| 643 "&idlabel_prefix."|| 644 put(&idlabel.,&idlabel_format)|| 645 %end; 646 %else %do; 647 " "||"&prefix."||strip(scan("&vars_char.",&i.))||"_"|| 648 "&idlabel_prefix."|| 649 put(&idlabel.,&idlabel_format), 650 %end; 651 %end; 652 &idlabel. 653 into :varlist_char separated by " ", 654 :junk 655 from &libname_in..&filename_in. (obs=&guessingrows.) 656 order by &idlabel. 657 ; 658 %let num_charvars=&sqlobs.; 659 %end; 660 661 %if &vars_num ne "" %then %do; 662 select distinct 663 %do i=1 %to %sysfunc(countw("&vars_num.")); 664 %if &i. lt %sysfunc(countw("&vars_num.")) %then %do; 665 " "||"&prefix."||strip(scan("&vars_num.",&i.))||"_"|| 666 "&idlabel_prefix."|| 667 put(&idlabel.,&idlabel_format)|| 668 %end; 669 %else %do; 670 " "||"&prefix."||strip(scan("&vars_num.",&i.))||"_"|| 671 "&idlabel_prefix."|| 672 put(&idlabel.,&idlabel_format), 673 %end; 674 %end; 675 &idlabel. 676 into :varlist_num separated by " ", 677 :junk 678 from &libname_in..&filename_in. (obs=&guessingrows.) 679 order by &idlabel. 680 ; 681 %let num_numvars=&sqlobs.; 682 %end; 683 684 create table _for_format as 685 select distinct &idlabel. as start 686 from &libname_in..&filename_in. (obs=&guessingrows.) 687 order by &idlabel. 688 ; 689 %let num_numlabels=&sqlobs.; 690 691 quit; 692 693 data _for_format; 694 set _for_format; 695 retain fmtname "labelfmt" type "N"; 696 label=_n_-1; 697 run; 698 699 700 proc format cntlin = _for_format; 701 run ; 702 703 filename sascode temp; 704 data _null_; 705 file sascode; 706 length var $32; 707 put "data &libname_out..&filename_out.;"; 708 put " set &libname_in..&filename_in.;"; 709 put " by &by_variable.;"; 710 %if &vars_char. ne "" %then %do; 711 put " array want_char(*) $"; 712 %do i=1 %to %eval(&num_numlabels.*%sysfunc(countw("&vars_char."))); 713 var=scan("&varlist_char.",&i.); 714 put @7 var; 715 %end; 716 put @5 ";"; 717 put " array have_char(*) $ &vars_char.;"; 718 put " retain want_char;"; 719 put " if first.&by_variable. then call missing(of want_char(*));"; 720 put " _nchar=put(&idlabel.,labelfmt.)*dim(have_char);"; 721 put " do _i=1 to dim(have_char);"; 722 put " want_char(_nchar+_i)=have_char(_i);"; 723 put " end;"; 724 %end; 725 %if &vars_num. ne "" %then %do; 726 put " array want_num(*) "; 727 %do i=1 %to %eval(&num_numlabels.*%sysfunc(countw("&vars_num."))); 728 var=scan("&varlist_num.",&i.); 729 put @7 var; 730 %end; 731 put @5 ";"; 732 put " array have_num(*) &vars_num.;"; 733 put " retain want_num;"; 734 put " if first.&by_variable. then call missing(of want_num(*));"; 735 put " _nnum=put(&idlabel.,labelfmt.)*dim(have_num);"; 736 put " do _i=1 to dim(have_num);"; 737 put " want_num(_nnum+_i)=have_num(_i);"; 738 put " end;"; 739 %end; 740 put " drop &idlabel. _: &vars.;"; 741 put " if last.&by_variable. then output;"; 742 put "run;"; 743 run; 744 %include sascode; 745 %mend transpose; 746 747 options NOQUOTELENMAX; 748 %transpose(filename_in =student_school, 749 by_variable=StdPseudoId, 750 vars=SchCode PctFRlunch--PctTotReclassYr, 751 idlabel=year, 752 idlabel_format=4., 753 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.22 seconds user cpu time 0.00 seconds system cpu time 0.01 seconds memory 349.00k OS Memory 18364.00k Timestamp 01/21/2013 05:08:08 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 3 rows and 1 columns. NOTE: PROCEDURE SQL used (Total process time): real time 0.20 seconds user cpu time 0.07 seconds system cpu time 0.03 seconds memory 1460.71k OS Memory 18364.00k Timestamp 01/21/2013 05:08:09 PM NOTE: There were 3 observations read from the data set WORK._FOR_FORMAT. NOTE: The data set WORK._FOR_FORMAT has 3 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.04 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 299.71k OS Memory 18364.00k Timestamp 01/21/2013 05:08:09 PM NOTE: Format LABELFMT has been output. NOTE: PROCEDURE FORMAT used (Total process time): real time 0.06 seconds user cpu time 0.01 seconds system cpu time 0.01 seconds memory 169.15k OS Memory 18364.00k Timestamp 01/21/2013 05:08:09 PM NOTE: There were 3 observations read from the data set WORK._FOR_FORMAT. NOTE: 72 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.03 seconds system cpu time 0.01 seconds memory 963.56k OS Memory 19436.00k Timestamp 01/21/2013 05:08:09 PM NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column). 819:11 NOTE: There were 170700 observations read from the data set WORK.STUDENT_SCHOOL. NOTE: The data set WORK.WANT has 81687 observations and 58 variables. NOTE: Compressing data set WORK.WANT decreased size by 18.97 percent. Compressed is 1892 pages; un-compressed would require 2335 pages. NOTE: DATA statement used (Total process time): real time 0.87 seconds user cpu time 0.37 seconds system cpu time 0.26 seconds memory 548.03k OS Memory 18876.00k Timestamp 01/21/2013 05:08:10 PM
... View more