title 'FPED Calculations'; title2 'ASA24-2011 and ASA24-2012-KIDS'; *************************************************************************************************************************************** *Overview - This program calculates FPED values for ASA24-2011 and ASA24-2012-KIDS study data * User must specify folder and file names in Step 1 * If folder and filenames are correct, no further user edits are required after Step 1 ***************************************************************************************************************************************; ***************************************************************************************** *STEP 1 - Specify the required input and resulting output data files * *****************************************************************************************; *Specify the location of the FPED excel file. This file is provided by NCI; libname fped xlsx '\\westat.com\dfs\ASA24SECONDARY\test\test\FPED for FNDDS4.1_merged.xlsx'; *Input data files: *Specify the full folder location of the files that were output from ASA24 or *If you have cleaned your data, specify both the full folder location and the filenames of the editted/cleaned versions. Note, the structure of the revised files must match the original structure; filename inf '\\westat.com\dfs\ASA24SECONDARY\test\test\SCAN_7864_INFMYPHEI.csv'; *location and name of INFMYPHEI csv file to be updated; filename tn '\\westat.com\dfs\ASA24SECONDARY\test\test\SCAN_7865_TNMYPHEI.csv'; *location and name of TNMYPHEI csv file to be updated; *Output data files: *Specify new INFMYPHEI and TNMYPHEI output csv files containing FPEDs; filename updinf '\\westat.com\dfs\ASA24SECONDARY\test\test\NewSCANINFMYPHEI.csv'; *location and name of updated INFMYPHEI csv file created by this program; filename updtn '\\westat.com\dfs\ASA24SECONDARY\test\test\NewSCANTNMYPHEI.csv'; *location and name of updated TNMYPHEI csv file created by this program; *NOTE: IF ALL DATASETS AND FILES ARE NAMED AND SPECIFIED CORRECTLY, NO MODIFICATIONS ARE NEEDED FROM THIS POINT FORWARD; ********************************************************************************************* *STEP 2: This step prepares the file containing the FPED values * *********************************************************************************************; data fped (keep=foodcode modcode F_CITMLB--A_DRINKS); set FPED.SHEET1; run; proc sort data=fped; by foodcode modcode; ********************************************************************************************* *STEP 3: This step prepares the file containing the intake information (INFMYPHEI) * *********************************************************************************************; *Input the 2011 or KIDS-2012 ASA24 INFMYPHEI file; proc import datafile=inf out=inf dbms=csv replace; getnames=yes; run; *From original INFMYPHEI file, make three data sets. The first 92 variables which will not change, the middle variables that will be replaced based on food and food amount consumed, and the last two variables. Each data set will also include a variable that maintains the original order of information (origord); data first (keep=origord UserName--EQUIVFLAG) new (keep=origord foodcode modcode FoodAmt) last (keep=origord foodcomp food_description); set inf; origord=_n_; output first; output new; output last; run; *Sort new file by foodcode and modcode; proc sort data=new; by foodcode modcode; run; *Merge new file with fped file to get amount of fped per 100 grams of foodcode/modcode; data new; merge new (in=i) fped (in=d); by foodcode modcode; if i; run; *Calculate amount of fped for each item based on grams of food consumed - DO NOT MODIFY - ARRAY CAPTURES ALL NUTRIENTS AND FOOD GROUPS IN FILE; data new; set new; keep origord F_CITMLB--A_DRINKS; ARRAY FPED (J) F_CITMLB--A_DRINKS; DO OVER FPED; FPED=FPED*FoodAmt/100; END; run; *Sort the three data sets back into original order; proc sort data=first; by origord; proc sort data=new; by origord; proc sort data=last; by origord; *Merge the three data sets back together into one data set; data updinf (drop=origord); merge first new last; by origord; ********************************************************************************************* *STEP 4: This step creates updated INFMYPHEI and TNMYPHEI files and saves them to the location *specified in Step 1. *********************************************************************************************; *Create the new INFMYPHEI CSV file which includes FPED values; proc export data=updinf file=updinf dbms=csv replace; run; *Sort the INFMYPHEI data by person/day identifiers to get daily totals; proc sort data=updinf; by UserName RecallNo; run; *Get daily totals by person and day of FPED values - DO NOT MODIFY - ARRAY CAPTURES ALL NUTRIENTS IN FILE; proc means data=updinf noprint; by UserName RecallNo; var F_CITMLB--A_DRINKS; output out=tot sum= ; run; *Input the ASA24 total nutrient and MyPyramid Equivalennts (TNMYPHEI) file; proc import datafile=tn out=tn dbms=csv replace; getnames=yes; run; *From original TNMYPHEI file, make two data sets. The first 92 variables which will not change, and the last variable. Each data set will also include UserName RecallNo; data tfirst (keep=UserName--B12_ADD origord) tlast (keep=UserName RecallNo datacomp); set tn; origord=_n_; output tfirst; output tlast; run; *Sort the two data sets back into order; proc sort data=tfirst; by UserName RecallNo; run; proc sort data=tlast; by UserName RecallNo; run; *Merge original TNMYPHEI variables with new daily totals; data tn; merge tfirst tot tlast; by UserName RecallNo; run; *Sort back into original order; proc sort data=tn; by origord; run; data updtn (drop=_type_ _freq_ origord); set tn; *Create the new TNMYPHEI file which includes FPED values; proc export data=updtn file=updtn dbms=csv replace; run; *If the program ran successfully, you now have 2 new files - INFMYPHEI and TNMYPHEI - with updated FPED values ; *END;