Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    Looping through a set of non-consecutive numbers for an export file

    Good morning community ,
    I am seeking assistance with exporting data using a loop. I would like the tabs to be named by the sport_name, but I started off with naming the sheet by its sport_code to get it working. I receive an "invalid syntax error code" when I run the code listed below. What am I doing wrong and what edits can I make to achieve my goal? Thank you for your time and feedback!


    forvalues i= 6 10 11 14 16 20 22 30 31 36 53 55 56 57 58 59 61 64 68 69 {

    export excel dbn psal_name psal_program_address sport_name total_enrollment enrollment_by_gender___male enrollment_by_gender____female sapid sap_school team_N iap_priority using "IAP_Mapping_Data.xls" if sport_code==`i', sheet(`i') sheetreplace firstrow(variables)

    }







    . dataex psal_code sport_code sport_name

    ----------------------- copy starting from the next line -----------------------
    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input long psal_code byte sport_code str43 sport_name
1420  6 "Baseball Boys Varsity"      
1420 22 "Softball Girls Varsity"     
1538 68 "Badminton Girls Varsity"    
1538 22 "Softball Girls Varsity"     
1538 55 "Fencing Girls Varsity"      
1552 16 "Volleyball Boys Varsity"    
1569 31 "Outdoor Track Girls"        
1569 30 "Outdoor Track Boys"         
1569  6 "Baseball Boys Varsity"      
1569 14 "Tennis Boys Varsity"        
2047 30 "Outdoor Track Boys"         
2047 69 "Stunt Coed Varsity"         
2260 30 "Outdoor Track Boys"         
2350 30 "Outdoor Track Boys"         
2350 22 "Softball Girls Varsity"     
2350 31 "Outdoor Track Girls"        
2350 11 "Handball Boys Varsity"      
2350 61 "Flag Football Girls Varsity"
2358  6 "Baseball Boys Varsity"      
2358 22 "Softball Girls Varsity"     
2370 22 "Softball Girls Varsity"     
2378 22 "Softball Girls Varsity"     
2378  6 "Baseball Boys Varsity"      
2380 11 "Handball Boys Varsity"      
2380  6 "Baseball Boys Varsity"      
2380 22 "Softball Girls Varsity"     
2380 20 "Handball Girls Varsity"     
2380 31 "Outdoor Track Girls"        
2380 61 "Flag Football Girls Varsity"
2436 16 "Volleyball Boys Varsity"    
2436 20 "Handball Girls Varsity"     
2436 22 "Softball Girls Varsity"     
2436  6 "Baseball Boys Varsity"      
2465 30 "Outdoor Track Boys"         
2465 31 "Outdoor Track Girls"        
2465 61 "Flag Football Girls Varsity"
2465 22 "Softball Girls Varsity"     
2465  6 "Baseball Boys Varsity"      
2465 10 "Golf Coed Varsity"          
2513 16 "Volleyball Boys Varsity"    
2516 11 "Handball Boys Varsity"      
2516 22 "Softball Girls Varsity"     
2516 68 "Badminton Girls Varsity"    
2517  6 "Baseball Boys Varsity"      
2517 22 "Softball Girls Varsity"     
2517 68 "Badminton Girls Varsity"    
2517 11 "Handball Boys Varsity"      
2517 64 "Wrestling Girls Varsity"    
2517 16 "Volleyball Boys Varsity"    
2519 36 "Lacrosse Boys Varsity"      
2519 16 "Volleyball Boys Varsity"    
2519 10 "Golf Coed Varsity"          
2519 68 "Badminton Girls Varsity"    
2519 31 "Outdoor Track Girls"        
2519 30 "Outdoor Track Boys"         
2519 20 "Handball Girls Varsity"     
2519 14 "Tennis Boys Varsity"        
2519 22 "Softball Girls Varsity"     
2519 56 "Cricket Coed Varsity"       
2519 53 "Lacrosse Girls Varsity"     
2519 11 "Handball Boys Varsity"      
2519  6 "Baseball Boys Varsity"      
2519 69 "Stunt Coed Varsity"         
2519 55 "Fencing Girls Varsity"      
2521  6 "Baseball Boys Varsity"      
2521 11 "Handball Boys Varsity"      
2521 22 "Softball Girls Varsity"     
2521 16 "Volleyball Boys Varsity"    
2522 14 "Tennis Boys Varsity"        
2522  6 "Baseball Boys Varsity"      
2522 16 "Volleyball Boys Varsity"    
2523  6 "Baseball Boys Varsity"      
2523 22 "Softball Girls Varsity"     
2524 16 "Volleyball Boys Varsity"    
2524 22 "Softball Girls Varsity"     
2531 55 "Fencing Girls Varsity"      
2531 22 "Softball Girls Varsity"     
2531  6 "Baseball Boys Varsity"      
2531 16 "Volleyball Boys Varsity"    
2533 16 "Volleyball Boys Varsity"    
2533 31 "Outdoor Track Girls"        
2533 68 "Badminton Girls Varsity"    
2533 69 "Stunt Coed Varsity"         
2533 64 "Wrestling Girls Varsity"    
2533 22 "Softball Girls Varsity"     
2538 14 "Tennis Boys Varsity"        
2538 20 "Handball Girls Varsity"     
2538  6 "Baseball Boys Varsity"      
2538 16 "Volleyball Boys Varsity"    
2538 22 "Softball Girls Varsity"     
2541 11 "Handball Boys Varsity"      
2541 61 "Flag Football Girls Varsity"
2541 31 "Outdoor Track Girls"        
2541 30 "Outdoor Track Boys"         
2541 16 "Volleyball Boys Varsity"    
2541  6 "Baseball Boys Varsity"      
2541 22 "Softball Girls Varsity"     
2546  6 "Baseball Boys Varsity"      
2546 22 "Softball Girls Varsity"     
2546 30 "Outdoor Track Boys"         
end
    ------------------ copy up to and including the previous line ------------------

    Tags: None
  • #2
    Originally posted by Rhina Torres View Post

    forvalues i= 6 10 11 14 16 20 22 30 31 36 53 55 56 57 58 59 61 64 68 69 {
    needs to be

    Code:
    foreach i of numlist 6 10 11 14 16 20 22 30 31 36 53 55 56 57 58 59 61 64 68 69 {
    See

    Code:
    help foreach

    Comment

    • #3
      If you use help forvalues, you can see that it only takes some kind of range:

      Code:
              forvalues lname = range {
                Stata commands referring to `lname'
        }

    where range is
                                                           #1(#d)#2      meaning #1 to #2 in steps of #d    
                                                           #1/#2         meaning #1 to #2 in steps of 1      
                                                           #1 #t to #2   meaning #1 to #2 in steps of #t - #1
                                                           #1 #t :  #2   meaning #1 to #2 in steps of #t - #1
      Your sequence is not an expression of range, so "foreach i in 6 10 11 14 16 20 22 30 31 36 53 55 56 57 58 59 61 64 68 69" would be better here.

      To shorten it even more, and make it more flexible, try:

      Code:
      levelsof sport_code, local(sc)
foreach i of local sc{
*whatever command here
}
      Last edited by Ken Chui; 20 Feb 2024, 10:58.

      Comment

      • #4
        Good explanation in #2 and #3. I think you are looking for something like this:

        Code:
        levelsof sport_name, local(teams)
foreach team of local teams {
 export excel psal_code sport_code sport_name using "IAP_Mapping_Data.xls" ///
    if sport_name=="`team'", sheet("`team'") sheetreplace ///
    firstrow(variables)
}

        Comment

        • #5
          Thank you so much for clarifying !


          Originally posted by Andrew Musau View Post

          needs to be

          Code:
          foreach i of numlist 6 10 11 14 16 20 22 30 31 36 53 55 56 57 58 59 61 64 68 69 {
          See

          Code:
          help foreach

          Comment

          • #6
            Thank you so much for your feedback and advanced code!

            Originally posted by Ken Chui View Post
            If you use help forvalues, you can see that it only takes some kind of range:

            Code:
            forvalues lname = range {
Stata commands referring to `lname'
}

where range is
#1(#d)#2 meaning #1 to #2 in steps of #d
#1/#2 meaning #1 to #2 in steps of 1
#1 #t to #2 meaning #1 to #2 in steps of #t - #1
#1 #t : #2 meaning #1 to #2 in steps of #t - #1
            Your sequence is not an expression of range, so "foreach i in 6 10 11 14 16 20 22 30 31 36 53 55 56 57 58 59 61 64 68 69" would be better here.

            To shorten it even more, and make it more flexible, try:

            Code:
            levelsof sport_code, local(sc)
foreach i of local sc{
*whatever command here
}

            Comment

            Previous Next
            Working...
            X