Sas Homework ( Requires Showing The Codes And Commenting)

ECON 62052/72052 Data Preparation for Academic Research Jooyoun Park 1 Homework 6. SAS Data Prep #2 Deadline: November 22nd (Tue) < Total points: 85 pts > Assignment instruction: 1. Submit your SAS program file, output file as a PDF, and the log file as log. 2. Name your files: SAS program [your_last_name]_hw6.sas Output: [your_last_name]_hw6_output.pdf Log: [your_last_name]_hw6.log 3. Write all codes in one do file but separate each task by using comments to improve visibility. 4. Log file and output file should be from one run of a completed SAS program. Finish your codes. Close the log and result window. Then run your SAS program in its entirety to create the log and output. Or you can close SAS and open it again and just run the completed SAS program. (If not, I will subtract 10 points from your grade.) 5. Comment what you are doing generously so I can understand what you are doing. That is, comment for me, not just for your own reference. 6. If a question requires you to write answers that are not codes, write them as comments on the SAS program. 7. AVOID PLAGIARISM AT ALL COST! Put away my in-class program files while you are coding, then you can’t possibly plagiarize. 8. ADD TITLE TO ALL TABULATIONS, SUMMARY STATS, AND ETC.. TASK 5 (rate 7): SIC code validation There are three sic code variables - sic1, sic2, and sic3. Validate all three variables using sic88_97.txt file. a. sic88_97.txt file has industry group headings and TOTAL tings. You first need to clean up sic88_97.txt file and make a nice SAS data file that only contains the valid sic codes. b. Validate sic1, sic2, and sic3 using that sic code file you just made. i. Create validity indicators named sic1_valid, sic2_valid, and sic3_valid in the process. Label the variables. ii. Each step, tabulate the validity indicator. Add a comment to show what % of the sic codes are valid. iii. As you proceed, clean up the invalid sic codes. Invalid codes must not remain in the data file. d. Organize sic1, sic2, and sic3. i. Remove duplicates: If there are same sic codes among sic1, sic2, sic3, remove the duplicates. ii. Reorganize sic1, sic2, and sic3 so that first code appears as sic1, second as sic2, third as sic3. That is: - no observation should have sic1 missing when sic2 or sic3 is not missing. - no observation should have sic2 missing when sic3 is not missing. ECON 62052/72052 Data Preparation for Academic Research Jooyoun Park 2 TASK 6 (rate 6): NAICS code validation for the relevant version In class, we validated naicscode without taking naicscodeyear into account. naicscodeyear shows which version the naicscode are coded with. Here, we’ll validate naicscode using the correct version of the NAICS codes. For that, I provided five excel files, • naics2002.xlsx for naicscode with naicscodeyear=2002 : 134 observations • naics2007.xlsx for naicscode with naicscodeyear=2007 : 2,273 observations • naics2012.xlsx for naicscode with naicscodeyear=2012 : 354 observations • naics2017.xlsx for naicscode with naicscodeyear=2017 : 2,085 observations • naics2022.xlsx for naicscode with naicscodeyear=2022 : 17,824 observations This process will be different from TASK 5 because the different versions of NAICS codes are all in one variable, naicscode. For instance, 134 observations have naicscode that belong to 2002 version of NAICS, so those observations should be validated using naics2002 codes. 2,273 observations belong to 2007 version and should be validated with naics2007 file. And so on. a. Import all five excel files and create SAS data files that are ready to be merged with the TAA petition file. b. Validate naicscode using the relevant version of the NAICS code file. Start with 2002 and show the process. Comment to explain each step. i. Create a validity indicator named naics2002_valid in the process. Label the variable. ii. Tabulate the indicator. Add a comment to show what % of the naics codes are valid. (It’s possible to have 100% validity rate. Don’t panic if you only see value 1.) iii. Clean up the invalid codes. c. Repeat this process for 2007, 2012, 2017, and 2022. - Once you finish coding part (b), you can copy and paste that code to repeat the process for the remaining four versions. - Or, you could try using a macro. Since I showed you how macro works very briefly, you don’t have to use a macro. But if you can (or you want), feel free. You will see how convenient it is. TASK 7 (rate 4): Clean up countries The variable countries is 47-digit character variable ($47.). Some have long names, but some have multiple countries listed. Clean up the variable. a. Split the variable into a few variables if there are more than one country recorded. The maximum number of countries listed is 4. Create country1, …, country4 in the order they are listed. b. Clean up errors in country names. (Feel free to refer to your STATA code on this task.) - Delete the values that are not really countries: e.g. Pacific Rim Countries - In case there is a variety of naming conventions, choose one: e.g. Republic of Korea, South Korea - Convert Korea to South Korea. - Fix typos. c. After cleaning each, re-organize countries1, countries2, countries3, and countries4. - Check if there are same countries among countries 1 to 4. Clean them up if there’s any. - Fill the countries names from country1 to country4 in the same way we reorganized sic1, sic2, and sic3.