Hello,
I am getting this error message and I am unsure what I need to do to fix it. Any ideas? Thank you for any help!
/*
DSQ Self-Administered Questionnaire: Paper
SAS program to process self-administered paper questionnaire
*/
filename yourdata '/folders/myfolders/sasuser.v94/Diet_data_new.csv';
proc import datafile=yourdata
out=work.yourdata
dbms=csv
replace;
run;
/*
The following files are contained within the archive that
contained this analysis program. Once extracted from the archive,
these files should be located in the same folder/directory as
the analysis program.
*/
libname ntile xlsx '/folders/myfolders/Updated/calib.DSQ.cereal.ntile.xlsx';
libname psize xlsx '/folders/myfolders/Updated/calib.equation.coeff.xlsx';
libname rcoeff xlsx '/folders/myfolders/Updated/calib.portion.size.xlsx';
proc format;
value gender 1='male'
2='female';
run;
data dtq;
set yourdata;
/*this program uses variables names:
DSQ_xx1
DSQ_xx2
DSQ_010, DSQ_030, etc
***PLEASE NOTE SPECIAL CEREAL RECODING IS REQUIRED BEFORE RUNNING THIS CODE****
DSQ_020 and DSQ_xx3 MUST be converted to an 8 digit foodcode by the user
The current variables (DSQ_020 and DSQ_xx3) are character variables that represent
assorted cereals. Using the 'calib.DSQ.cereal.ntile.xlsx' file, the user
must create new variables, which will be called:
food_code_1 and
food_code_2
and contain an eight digit FNDDS foodcode which represents the particular cereal
that was reported. If only one cereal is reported, food_code_2 should remain blank.
*/
/*in this data step, create variables:
Required names include: ageinyr (age in years) gender (1=male 2=female)
and the food ffq - each food has two variables, times and units
*/
ageinyr=DSQ_xx1;
if DSQ_xx2='A' then gender=1;
else if DSQ_xx2='B' then gender=2;
cereal1fc=food_code_1;
cereal2fc=food_code_2;
/*create numcer to represent the number of cereals provided;
*/
if cereal1fc=. and cereal2fc=. then numcer=0;
else if cereal1fc > 0 and cereal2fc > 0 then numcer=2;
else if cereal1fc > 0 then numcer=1;
/*converting responses into times per day for food variables of interest;
*/
%macro foodrange (in,out,maxv);
if &in='A' then &out=0;
else if &in='B' then &out=0.033;
else if &in='C' then &out=0.083;
else if &in='D' then &out=0.143;
else if &in='E' then &out=0.286;
else if &in='F' then &out=0.5;
else if &in='G' then &out=0.786;
else if &in='H' then &out=1;
else if &in='I' then &out=2;
*top code outliers;
if &out > &maxv then &out=&maxv;
%mend foodrange;
%macro bevrange (in,out,maxv);
if &in='A' then &out=0;
else if &in='B' then &out=0.033;
else if &in='C' then &out=0.083;
else if &in='D' then &out=0.143;
else if &in='E' then &out=0.286;
else if &in='F' then &out=0.5;
else if &in='G' then &out=0.786;
else if &in='H' then &out=1;
else if &in='I' then &out=2.5;
else if &in='J' then &out=4.5;
else if &in='K' then &out=6;
*top code outliers;
if &out > &maxv then &out=&maxv;
%mend bevrange;
%foodrange(dsq_010,hccerxpd,7);
%bevrange(dsq_030,milkxpd,10);
%bevrange(dsq_040,sodaxpd,8);
%bevrange(dsq_050,frtjcxpd,8);
%bevrange(dsq_060,swtctxpd,10);
%bevrange(dsq_070,energyxpd,7);
%foodrange(dsq_080,fruitxpd,8);
%foodrange(dsq_090,saladxpd,5);
%foodrange(dsq_100,frfryxpd,5);
%foodrange(dsq_110,othpotxpd,3);
%foodrange(dsq_120,beanxpd,4);
%foodrange(dsq_130,othvegxpd,5);
%foodrange(dsq_140,pizzaxpd,2);
%foodrange(dsq_150,salsaxpd,3);
%foodrange(dsq_160,tomscxpd,2);
%foodrange(dsq_190,cheesexpd,6);
%foodrange(dsq_200,whgbrdxpd,6);
%foodrange(dsq_210,brricexpd,4);
%foodrange(dsq_220,candyxpd,8);
%foodrange(dsq_230,donutxpd,5);
%foodrange(dsq_240,cakexpd,7);
%foodrange(dsq_250,icecrmxpd,5);
%foodrange(dsq_260,popcornxpd,3);
label hccerxpd='number of times per day eat hot or cold cereal'
milkxpd='number of times per day drink milk'
sodaxpd='number of times per day drink soda'
frtjcxpd='number of times per day drink fruit juice'
swtctxpd='number of times per day drink sweet coffee/tea'
energyxpd='number of times per day drink fruit/sports/energy drink'
fruitxpd='number of times per day eat fruit'
saladxpd='number of times per day eat salad'
frfryxpd='number of times per day eat fried potatoes'
othpotxpd='number of times per day eat other potatoes'
beanxpd='number of times per day eat beans'
othvegxpd='number of times per day eat other vegetables'
pizzaxpd='number of times per day eat pizza'
salsaxpd='number of times per day eat salsa'
tomscxpd='number of times per day eat tomtato sauce'
cheesexpd='number of times per day eat cheese'
whgbrdxpd='number of times per day eat whole grain bread'
brricexpd='number of times per day eat cooked whole grain (brown rice)'
candyxpd='number of times per day eat candy'
donutxpd='number of times per day eat pastries'
cakexpd='number of times per day eat cookies/cake'
icecrmxpd='number of times per day eat ice cream'
popcornxpd='number of times per day eat pop corn';
if (2 <= ageinyr <= 3) then bcage=1;
else if (4 <= ageinyr <= 5) then bcage=2;
else if (6 <= ageinyr <= 7) then bcage=3;
else if (8 <= ageinyr <= 9) then bcage=4;
else if (10 <= ageinyr <= 11) then bcage=5;
else if (12 <= ageinyr <= 13) then bcage=6;
else if (14 <= ageinyr <= 15) then bcage=7;
else if (16 <= ageinyr <= 17) then bcage=8;
else if (18 <= ageinyr <= 25) then bcage=9;
else if (26 <= ageinyr <= 35) then bcage=10;
else if (36 <= ageinyr <= 45) then bcage=11;
else if (46 <= ageinyr <= 60) then bcage=12;
else if (61 <= ageinyr <= 69) then bcage=13;
else if (70 <= ageinyr <= 99) then bcage=14;
*make age dummy variables ****;
if (2 <= ageinyr <= 11) then kidgrp=1;
else kidgrp=0;
if (12 <= ageinyr <= 17) then teengrp=1;
else teengrp=0;
run;
data ntile;
set ntile.'CALIB.DSQ.CEREAL.NTILE'n;
run;
proc sort data=ntile;
by food_code;
run;
data ntile (keep=cereal1fc cereal2fc whgnt sugnt calcnt fibnt);
set ntile;
by food_code;
if first.food_code;
cereal1fc=food_code;
cereal2fc=food_code;
run;
proc sort data=dtq;
by cereal1fc;
run;
proc sort data=ntile;
by cereal1fc;
run;
data dtq (drop= whgnt sugnt calcnt fibnt);
merge dtq (in=d) ntile (drop=cereal2fc);
by cereal1fc;
if d;
c1whgnt=whgnt;
c1sugnt=sugnt;
c1calcnt=calcnt;
c1fibnt=fibnt;
run;
proc sort data=dtq;
by cereal2fc;
run;
proc sort data=ntile;
by cereal2fc;
run;
data dtq (drop= whgnt sugnt calcnt fibnt);
merge dtq (in=d) ntile (drop=cereal1fc);
by cereal2fc;
if d;
c2whgnt=whgnt;
c2sugnt=sugnt;
c2calcnt=calcnt;
c2fibnt=fibnt;
run;
data dtq;
set dtq;
if numcer in (0,1,2) and hccerxpd >= 0 then do;
wg1f=0; wg2f=0; wg3f=0;
as1f=0; as2f=0; as3f=0;
cm1f=0; cm2f=0; cm3f=0;
fb1f=0; fb2f=0; fb3f=0;
if numcer=1 then do;
if c1whgnt=1 then wg1f=wg1f+hccerxpd;
else if c1whgnt=2 then wg2f=wg2f+hccerxpd;
else if c1whgnt=3 then wg3f=wg3f+hccerxpd;
if c1sugnt=1 then as1f=as1f+hccerxpd;
else if c1sugnt=2 then as2f=as2f+hccerxpd;
else if c1sugnt=3 then as3f=as3f+hccerxpd;
if c1calcnt=1 then cm1f=cm1f+hccerxpd;
else if c1calcnt=2 then cm2f=cm2f+hccerxpd;
else if c1calcnt=3 then cm3f=cm3f+hccerxpd;
if c1fibnt=1 then fb1f=fb1f+hccerxpd;
else if c1fibnt=2 then fb2f=fb2f+hccerxpd;
else if c1fibnt=3 then fb3f=fb3f+hccerxpd;;
end;
else if numcer=2 then do;
if c1whgnt=1 then wg1f=wg1f+(.75*hccerxpd);
else if c1whgnt=2 then wg2f=wg2f+(.75*hccerxpd);
else if c1whgnt=3 then wg3f=wg3f+(.75*hccerxpd);
if c2whgnt=1 then wg1f=wg1f+(.25*hccerxpd);
else if c2whgnt=2 then wg2f=wg2f+(.25*hccerxpd);
else if c2whgnt=3 then wg3f=wg3f+(.25*hccerxpd);
if c1sugnt=1 then as1f=as1f+(.75*hccerxpd);
else if c1sugnt=2 then as2f=as2f+(.75*hccerxpd);
else if c1sugnt=3 then as3f=as3f+(.75*hccerxpd);
if c2sugnt=1 then as1f=as1f+(.25*hccerxpd);
else if c2sugnt=2 then as2f=as2f+(.25*hccerxpd);
else if c2sugnt=3 then as3f=as3f+(.25*hccerxpd);
if c1calcnt=1 then cm1f=cm1f+(.75*hccerxpd);
else if c1calcnt=2 then cm2f=cm2f+(.75*hccerxpd);
else if c1calcnt=3 then cm3f=cm3f+(.75*hccerxpd);
if c2calcnt=1 then cm1f=cm1f+(.25*hccerxpd);
else if c2calcnt=2 then cm2f=cm2f+(.25*hccerxpd);
else if c2calcnt=3 then cm3f=cm3f+(.25*hccerxpd);
if c1fibnt=1 then fb1f=fb1f+(.75*hccerxpd);
else if c1fibnt=2 then fb2f=fb2f+(.75*hccerxpd);
else if c1fibnt=3 then fb3f=fb3f+(.75*hccerxpd);
if c2fibnt=1 then fb1f=fb1f+(.25*hccerxpd);
else if c2fibnt=2 then fb2f=fb2f+(.25*hccerxpd);
else if c2fibnt=3 then fb3f=fb3f+(.25*hccerxpd);
end;
end;
run;
proc sort data=dtq;
by gender bcage;
run;
*this pulls in the portion size adjustment information by gender and agegrp;
data adjps;
set psize.'CALIB.PORTION.SIZE'n;
bcage=agegrp;
run;
Log:
602 *this pulls in the portion size adjustment information by gender and agegrp;
603 data adjps;
604 set psize.'CALIB.PORTION.SIZE'n;
ERROR: Couldn't find range or sheet in spreadsheet
ERROR: File PSIZE.'CALIB.PORTION.SIZE'n.DATA does not exist.
605 bcage=agegrp;
606 run;
I think you want to use the rcoeff libname, rather than the psize libname.
libname rcoeff xlsx '/folders/myfolders/Updated/calib.portion.size.xlsx';
We don't need your full code, just the relevant sections. The following workbook does not have the sheet name indicated. Make sure the cases match, it usually doesn't matter in SAS, but I'm not sure how that works for name literals, which you're using here.
libname psize xlsx '/folders/myfolders/Updated/calib.equation.coeff.xlsx';
This sheet is the problematic one:
CALIB.PORTION.SIZE
Hi @Reeza Thank you for the suggestion. I have gone through and changed the cases so they are lowercase. I am still getting the same error message. Any other suggestions? Thanks!
I think you want to use the rcoeff libname, rather than the psize libname.
libname rcoeff xlsx '/folders/myfolders/Updated/calib.portion.size.xlsx';
@SuzanneDorinski thank you!!!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.