Date:         Thu, 24 Jun 2004 09:53:18 +0200
Reply-To:     "Groeneveld, Jim" <jim.groeneveld@VITATRON.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Groeneveld, Jim" <jim.groeneveld@VITATRON.COM>
Subject:      Re: Do Loop in a macro numbers files and variables
Comments: To: Pat Moore <anpam@UAA.ALASKA.EDU>
Content-Type: text/plain; charset="iso-8859-1"

Hi Pat,

That can be done using several macro loops, but I would prefer PROC TRANSPOSE. I'll try to adapt your code, have a look at PROC TRANSPOSE yourself. The code below is *untested*! Not everything to adapt is clear to me, see the comments and try to make the adaptations yourself. If it does not work correctly immediately I may have forgotten something, but then, I presume, you'll get the idea and correct is yourself. Compare this amount of code with the code you would need with PROC TRANSPOSE; maybe that would be much less and no macro code at all. Good luck!

%macro Course2preq (course,courseno) / PARMBUFF;
  %LOCAL NofPreqs I ;
  %LET NofPreqs = 0;
  %LET I = 1;
  %LET Preq = %SCAN (&SYSPBUFF, &I, %STR(%(%)') );
  %LET PreqNo = %SCAN (&SYSPBUFF, %EVAL(&I+1), %STR(%(%)'));
  %WHILE (&Preq NE AND &PreqNo NE) %DO;
    %LOCAL Preq%EVAL((&I+1)/2) PreqNo%EVAL((&I+1)/2);
    %LET Preq%EVAL((&I+1)/2) = &Preq;
    %LET PreqNo%EVAL((&I+1)/2) = &PreqNo;
    %LET NofPreqs = %EVAL (&NofPreqs + 1);
    %LET I = %EVAL (&I + 2);
    %LET Preq = %SCAN (&SYSPBUFF, &I, ',');
    %LET PreqNo = %SCAN (&SYSPBUFF, %EVAL(&I+1), ',');
  %END;

*Sets up files for course and prerequisites;
 data course (keep=pidm
   coursesubj coursecrslev
   coursesecx coursegrnum coursesuccess)
  %DO I = 1 %TO &NofPreqs;
   preq&I (keep=preq&I.term pidm
   preq&I.subj preq&I.crslev
   preq&I.secx preq&I.grnum preq&I.success)
  %END;
   ;
 set besttry;

*Selects AY 2002-03 Course data;
 if subj = "&course" and substr(crslev,2)="&courseno"
 then do;
 *Selects only AY 2002-2003 Course completions;
  if ay='2002-2003';
 *Renames course variables for merge;
  courseterm=term;
  coursesubj=subj;
  coursecrslev=crslev;
  coursesecx=secx;
  coursegrnum=offgrnum;
  coursesuccess=success;
  output course;
 end;

* Each prerequisite dataset--all academic years;
  %DO I = 1 %TO &NofPreqs;
    else if subj = "&&&preq.&I" and substr(crslev,2)="&&&preq.&I.no"
    then do;
* renames variables for merge;
    preq&I.term=term;
    preq&I.subj=subj;
    preq&I.crslev=crslev;
    preq&I.secx=secx;
    preq&I.grnum=offgrnum;
    preq&I.success=success;
    output preq&I ;
   end;
  %END;

 run;

*Merges course and prerequisite datasets by pidm;
* You could build a long dataset name here based on all Preqs, while macro looping;
* but it might become too long. So a shorter rather fixed one has been choosen here;
* If you want it differently, write the macro loop yourself, firstly creating a local;
* empty macro variable, assigning it its own value concatenated with the additional;
* name parts and so on. Name choosen here is &course&courseno.All&NofPreqs.Preqs;
  %LOCAL Merged;
  %LET Merged = &course&courseno.All&NofPreqs.Preqs; %* dots disappear in name;
   data &Merged;
    merge course (in=a)
  %DO I = 1 %TO &NofPreqs;
    Preq&I
  %END;
    ;
    by pidm;
    if a;
    if preq1grnum le 5 then preq1success=0; * How would you extend this to more Preqs?;
  %DO I = 1 %TO &NofPreqs;
    if preq&I.success=. then preq&I.success=0;
  %END;
    if preq1success=1 and preq2success=1 then prereq=1; * How would you extend this to more Preqs?;
    else prereq=0; * How would you extend this to more Preqs?;
    if coursesuccess gt 0; * How would you extend this to more Preqs?;
   run;
 proc freq data=&Merged noprint;
  where coursesuccess gt 0;
  table prereq*coursegrnum /nopercent out=freqcnt;
 run;
%mend course2preq;

* Macro call;
%Course2Preq (yourCourse, yourCourseNo, yourPreq1, yourPreq1no, yourPreq2, yourPreq2no, .....,.....);

Regards - Jim.
--
.  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .

Jim Groeneveld, MSc.
Biostatistician
Science Team
Vitatron B.V.
Meander 1051
6825 MJ  Arnhem
Tel: +31/0 26 376 7365
Fax: +31/0 26 376 7305
Jim.Groeneveld@Vitatron.com
www.vitatron.com

Showing statistically significant differences between football teams,
generally all sporters, requires larger samples than usually applied.

[common disclaimer]


-----Original Message-----
From: Pat Moore [mailto:anpam@UAA.ALASKA.EDU]
Sent: Wednesday, June 23, 2004 23:41
To: SAS-L@LISTSERV.UGA.EDU
Subject: Do Loop in a macro numbers files and variables


SAS gurus,

In this code:

%macro Course2preq (course,courseno,preq1,preq1no,preq2,preq2no);
*Sets up files for course and prerequisites;
 data course (keep=pidm
   coursesubj coursecrslev
   coursesecx coursegrnum coursesuccess)
   preq1 (keep=preq1term pidm
   preq1subj preq1crslev
   preq1secx preq1grnum preq1success)
  preq2 (keep=preq2term pidm
   preq2subj preq2crslev
   preq2secx preq2grnum preq2success);
 set besttry;
*Selects AY 2002-03 Course data;
 if subj = "&course" and substr(crslev,2)="&courseno"
 then do;
 *Selects only AY 2002-2003 Course completions;
  if ay='2002-2003';
 *Renames course variables for merge;
  courseterm=term;
  coursesubj=subj;
  coursecrslev=crslev;
  coursesecx=secx;
  coursegrnum=offgrnum;
  coursesuccess=success;
  output course;
 end;
*first prerequisite dataset--all academic years;
 else if subj = "&preq1" and substr(crslev,2)="&preq1no"
 then do;
 *renames variables for merge;
  preq1term=term;
  preq1subj=subj;
  preq1crslev=crslev;
  preq1secx=secx;
  preq1grnum=offgrnum;
  preq1success=success;
  output preq1 ;
 end;
*Second prerequisite dataset--all academic years;
 else if subj = "&preq2" and substr(crslev,2)="&preq2no"
 then do;
 *renames variables for merge;
  preq2term=term;
  preq2subj=subj;
  preq2crslev=crslev;
  preq2secx=secx;
  preq2grnum=offgrnum;
  preq2success=success;
  output preq2 ;
 end;
 run;
*Merges course and prerequisite datasets by pidm;
 data &course&courseno&preq1&preq1no&preq2&preq2no;
  merge course (in=a) preq1 preq2;
  by pidm;
  if a;
  if preq1grnum le 5 then preq1success=0;
  if preq1success=. then preq1success=0;
  if preq2success=. then preq2success=0;
  if preq1success=1 and preq2success=1 then prereq=1;
  else prereq=0;
  if coursesuccess gt 0;
 run;
 proc freq data=&course&courseno&preq1&preq1no&preq2&preq2no
noprint;
  where coursesuccess gt 0;
  table prereq*coursegrnum /nopercent out=freqcnt;
 run;
%mend course2preq;

How can I build a do loop to get rid of the repeated steps for preq2 and
set it up so it can repeat n times, renumbering the output file each time
(e.g., to produce output files entitled preq1, preq2, preq3, etc., each
having variables with the filename digit in them (e.g., the third pass
through the loop would produce an output file preq3 containing preq3term,
preq3subj, preq3crslev, etc.)?

The object is to merge to create a dataset consisting of one line per
student for each course which contains his/her grade in that course and in
all its prerequisites:

id     course course course preq1 preq1  preq1  preq2  preq2   preq2
number subj   crslev grnum  subj  crslev grnum  subj   crslev  grnum
stud1  ACCT   202    3      ACCT  101    2      ACCT   102     3
stud1  MATH   200    4      MATH  107    3      .      .       .
stud2  HIST   212    2      SOC   101    4      HIST   105     3
etc.

As shown, Math 200 has only one prerequisite, but the other two courses
have two each. Believe it or not, some of our courses have 18!

Thanks,

Pat Moore