I am trying to export a dataset of multiple NHANES variables along with their codes and frequencies that I assembled in SAS using SAS OnDemand for Academics. I've tried the following coding but in both instances nothing is appearing in the designated folder despite no error code appearing in the log.
Approach #1:
%CreateDataDictionary(lib=work, data=nhanes_demo, out=nhanes_dict);
ods excel file="/home/u58028236/myfolders/nhanes_dict.xlsx" options(sheet_name="Data Dictionary");
proc print data=nhanes_dict noobs label;
run;
ods excel close;
Approach #2:
PROC EXPORT
data=data_dict_labeled
outfile="/home/u58028236/Data Dictionary/test.csv"
DBMS=csv
replace;
run;
Are there errors in my code? Does SAS OnDemand for Academics not allow this function? Note that I've tried exporting to both a folder on my desk top and a folder in the SAS OnDemand folder Academics cloud. Nothing seems to work.
Many thanks to everyone who responded to my question -- the responses were very helpful. I was able to make tweaks to my code based on them and now I am able to export Excel tables. I really appreciate the comments about interpreting the log and making inline comments. I don't often see this type of very useful information!
What does the LOG show when you run these codes?
It is usually best to include LOG of code you are asking about. Copy the text from the log including all the messages or notes. Then on the forum open a text box clicking on the </> icon above the main message window and paste the text. The log will often show why no output was created.
My first suspect would be to double check and see if the data set Nhanes_dict was created and has any observations.
After reading your response and re-reviewing my code, I'm starting to think the problem may be an error in the code that created the dataset I am trying to export, but I still can't figure out what it might be. I've included both the code (pared down to two variables for the sake of brevity) and the log.
********************************************************************************; *RENAME VARIABLES IN A NEW DATASET ; ********************************************************************************; data work.data_dict; /*Rename variables*/ set work.data_all(rename=( sddsrvyr = cycle ridstatr = status riagendr = sex ridreth1 = race_eth dmdmartl = marital )); run; ********************************************************************************; *GENERATE FREQUENCIES ; ********************************************************************************; *INTERVIEW AND MEC STATUS; /* Step 1: Frequency of variable by cycle */ proc freq data=work.data_dict noprint; tables cycle * status / out=freq_status(drop=percent); run; /* Step 2: Standardize and add variable name */ data freq_status; length variable $32; set freq_status(rename=(status = value count = count)); variable = "status"; run; /* Step 3: View or export */ proc print data=freq_status; title "Frequencies for status by cycle"; run; *SEX; /* Step 1: Frequency of variable by cycle */ proc freq data=work.data_dict noprint; tables cycle * sex / out=freq_sex(drop=percent); run; /* Step 2: Standardize and add variable name */ data freq_sex; length variable $32; set freq_sex(rename=(sex = value count = count)); variable = "sex"; run; /* Step 3: View or export */ proc print data=freq_sex; title "Frequencies for sex by cycle"; run; *RACE-ETHNICITY; /* Step 1: Frequency of variable by cycle */ proc freq data=work.data_dict noprint; tables cycle * race_eth / out=freq_race_eth(drop=percent); run; /* Step 2: Standardize and add variable name */ data freq_race_eth; length variable $32; set freq_race_eth(rename=(race_eth = value count = count)); variable = "race_eth"; run; /* Step 3: View or export */ proc print data=freq_race_eth; title "Frequencies for race_eth by cycle"; run; ********************************************************************************; *ADD VALUE LABELS TO CODES ; ********************************************************************************; *DEFINE THE FORMATS; proc format; value cyclef 5 = "2007-08" 6 = "2009-10" 7 = "2011-12" 8 = "2013-14" 9 = "2015-16" 10 = "2017-18"; value statusf 1 = "Interviewed only" 2 = "Both interviewed and MEC examined"; value sexf 1 = "Male" 2 = "Female"; value race_ethf 1 = "Mexican American" 2 = "Other Hispanic" 3 = "Non-Hispanic White" 4 = "Non-Hispanic Black" 5 = "Other Race - Including Multi-Racial"; run; *APPLY THE FORMATS; data work.data_dict_labeled; set work.data_dict; format cycle cyclef. status statusf. sex sexf. race_eth race_ethf. ; run; *****************************************************************************************; *Create a combined long-format frequency table of multiple variables by survey cycle *; * that include the NHANES cycle, name, values, and frequency counts for each variable *; *; *****************************************************************************************; *Step 1: Create a global macro variable (&vars) with the list of variables being processed; %let vars = status sex race_eth; *Step 2: Create an empty output table; data all_freqs; length cycle 8 variable $32 value $100 count 8; stop; run; *Step 3: Define the macro to loop over variables; %macro freq_by_cycle; %local i vars; *Step 4: Loop through the variable list; %let i = 1; %do %while(%scan(&vars, &i) ne ); %let var = %scan(&vars, &i); *Step 5: Create a frequency table; proc freq data=work.data_dict_labeled noprint; tables cycle * &vars / out=freq_temp(drop=percent); run; *Step 6: Reshape the output; data freq_temp; set freq_temp; length variable $32 value $100; variable = "&vars"; value = vvaluex(symget("vars")); keep cycle variable value count; run; *Step 7: Append the result; proc append base=all_freqs data=freq_temp force; run; *Step 8: Finish the loop; %let i = %eval(&i + 1); %end; %mend freq_by_cycle; *Step 9: Run the macro; %freq_by_cycle; *EXPORT THE NEW DATASET; %CreateDataDictionary(lib=work, data=data_dict_labeled, out=nhanes_dict); ods excel file="/home/u58028236/myfolders/nhanes_dict.xlsx" options(sheet_name="Data Dictionary"); proc print data=nhanes_dict noobs label; run; ods excel close; proc export data=all_freqs outfile="~/myfolders/freq_status.csv" dbms=csv replace; run;
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 2 TITLE; NOTE: The quoted string currently being processed has become more than 262 bytes long. You might have unbalanced quotation marks. 3 FOOTNOTE; 4 OPTIONS LOCALE=en_US DFLANG=LOCALE; 5 DATA _NULL_; 6 RUN; 7 OPTIONS VALIDVARNAME=ANY; 8 OPTIONS VALIDMEMNAME=EXTEND; 86 &GRAPHTERM; ;*';*";*/;RUN;QUIT; _________________ 49 NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space between a quoted string and the succeeding identifier is recommended. 9 FILENAME _HTMLOUT TEMP; 10 FILENAME _GSFNAME TEMP; 11 FILENAME _DATAOUT TEMP; 12 %LET SYSCC=0; 13 %LET _CLIENTAPP='SAS Studio'; 14 %LET _CLIENTAPPABREV=Studio; 15 %LET _CLIENTAPPVERSION=3.81; 16 %LET _CLIENTVERSION=3.81; NOTE: The quoted string currently being processed has become more than 262 bytes long. You might have unbalanced quotation marks. 17 %LET _CLIENTMODE=wip; 18 %LET _SASSERVERNAME=%BQUOTE(SASApp); 19 %LET _SASHOSTNAME=%BQUOTE(odaws01-usw2); 20 %LET _SASPROGRAMFILEHOST=%BQUOTE(odaws01-usw2); 21 %LET _CLIENTUSERID=%BQUOTE(u58028236); 22 %LET _CLIENTUSERNAME=%BQUOTE(u58028236); 23 %LET CLIENTMACHINE=%BQUOTE(71.178.159.63); 24 %LET _CLIENTMACHINE=%BQUOTE(71.178.159.63); 25 %let SASWORKLOCATION="%sysfunc(getoption(work))/"; 26 FILENAME _CWD '.' _ 49 26 ! ; NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space between a quoted string and the succeeding identifier is recommended. 27 DATA _NULL_; 28 CALL SYMPUT('_SASWORKINGDIR',PATHNAME('_CWD')); ____________ 49 NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space between a quoted string and the succeeding identifier is recommended. 29 RUN; 30 FILENAME _CWD; NOTE: The quoted string currently being processed has become more than 262 bytes long. You might have unbalanced quotation marks. 31 32 %LET _SASPROGRAMFILE = %NRQUOTE(%NRSTR(/home/u58028236/Replications/Bhargava_2025/Data 32 ! Dictionary/Data_Dictionary_SAS_Code_abbreviated.sas)); 33 %LET _BASEURL = %BQUOTE(https://odamid-usw2.oda.sas.com/SASStudio/); 28 CALL SYMPUT('_SASWORKINGDIR',PATHNAME('_CWD')); ____ 49 NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space between a quoted string and the succeeding identifier is recommended. 34 %LET _EXECENV=SASStudio; 35 DATA _NULL_; 36 CALL SYMPUT("GRAPHINIT",""); 37 CALL SYMPUT("GRAPHTERM",""); 38 RC=TSLVL('SASXGOPT','N'); 39 _ERROR_=0; 40 IF (RC^=' ') THEN DO; 41 CALL SYMPUT("GRAPHINIT","GOPTIONS RESET=ALL GSFNAME=_GSFNAME;"); 42 CALL SYMPUT("GRAPHTERM","GOPTIONS NOACCESSIBLE;"); 43 END; 44 RUN; 45 DATA _NULL_; 46 RC=SYSPROD("PRODNUM002"); 47 IF (RC^=1) THEN DO; NOTE: The quoted string currently being processed has become more than 262 bytes long. You might have unbalanced quotation marks. 48 CALL SYMPUT("GRAPHINIT",""); 49 CALL SYMPUT("GRAPHTERM",""); 50 END; 51 RUN; 52 %LET _DATAOUT_MIME_TYPE=; 53 %LET _DATAOUT_NAME=; 54 %LET _DATAOUT_TABLE=; 55 %LET _DATAOUT_URL=; 56 %SYMDEL _DATAOUT_MIME_TYPE _DATAOUT_NAME _DATAOUT_URL _DATAOUT_TABLE; 57 %LET _SASWS_ = %BQUOTE(/home/u58028236); 40 IF (RC^=' ') THEN DO; ___________ 49 NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space between a quoted string and the succeeding identifier is recommended. 58 %LET _SASWSTEMP_=%BQUOTE(/home/u58028236/.sasstudio/.images/73bf4c39-6975-4bd6-9304-c7206d68048f); 59 ODS LISTING CLOSE; 60 ODS AUTONAVIGATE OFF; 61 ODS GRAPHICS ON; 62 ODS HTML5 (ID=WEB) DEVICE=PNG GPATH="&_SASWSTEMP_" ENCODING=utf8 FILE=_HTMLOUT (TITLE='Results: 62 ! Data_Dictionary_SAS_Code_abbreviated.sas') STYLE=Htmlblue OPTIONS(BITMAP_MODE='INLINE' OUTLINE='ON' SVG_MODE='INLINE' _______________________________________ ___________ ____________ 49 49 49 62 ! CSS_PREFIX='.ods_73bf4c39-6975-4bd6-9304-c7206d68048f' BODY_ID='div_73bf4c39-6975-4bd6-9304-c7206d68048f' ); NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space between a quoted string and the succeeding identifier is recommended. 63 &GRAPHINIT; 64 OPTIONS FIRSTOBS=1; 65 OPTIONS OBS=MAX; 66 OPTIONS DTRESET DATE NUMBER NOTES; NOTE: The quoted string currently being processed has become more than 262 bytes long. You might have unbalanced quotation marks. 67 %put %bquote(NOTE: ODS statements in the SAS Studio environment may disable some output features.); 68 OPTIONS NOTES STIMER SOURCE NOSYNTAXCHECK; 69 70 data work.data_dict; 71 /*Rename variables*/ 72 set work.data_all(rename=( 73 sddsrvyr = cycle 74 ridstatr = status 75 riagendr = sex 76 ridreth1 = race_eth 77 dmdmartl = marital 78 )); 79 run; 80 81 82 ********************************************************************************; 83 *GENERATE FREQUENCIES; 84 ********************************************************************************; 85 86 87 *INTERVIEW AND MEC STATUS; 88 89 /* Step 1: Frequency of variable by cycle */ 90 proc freq data=work.data_dict noprint; 91 tables cycle * status / out=freq_status(drop=percent); 92 run; 93 94 /* Step 2: Standardize and add variable name */ 95 data freq_status; 96 length variable $32; 97 set freq_status(rename=(status = value count = count)); 98 variable = "status"; 99 run; 100 101 /* Step 3: View or export */ 102 proc print data=freq_status; title "Frequencies for status by cycle"; run; 103 104 105 *SEX; 106 107 /* Step 1: Frequency of variable by cycle */ 108 proc freq data=work.data_dict noprint; 109 tables cycle * sex / out=freq_sex(drop=percent); 110 run; 111 112 /* Step 2: Standardize and add variable name */ 113 data freq_sex; 114 length variable $32; 115 set freq_sex(rename=(sex = value count = count)); 116 variable = "sex"; 117 run; 118 119 /* Step 3: View or export */ 120 proc print data=freq_sex; title "Frequencies for sex by cycle"; run; 121 122 123 124 125 *RACE-ETHNICITY; 126 127 /* Step 1: Frequency of variable by cycle */ 128 proc freq data=work.data_dict noprint; 129 tables cycle * race_eth / out=freq_race_eth(drop=percent); 130 run; 131 132 /* Step 2: Standardize and add variable name */ 133 data freq_race_eth; 134 length variable $32; 135 set freq_race_eth(rename=(race_eth = value count = count)); 136 variable = "race_eth"; 137 run; 138 139 /* Step 3: View or export */ 140 proc print data=freq_race_eth; title "Frequencies for race_eth by cycle"; run; 141 142 143 144 145 ********************************************************************************; 146 *ADD VALUE LABELS TO CODES; 147 ********************************************************************************; 148 149 *DEFINE THE FORMATS; 150 151 proc format; 152 153 value cyclef 154 5 = "2007-08" 155 6 = "2009-10" 156 7 = "2011-12" 157 8 = "2013-14" 158 9 = "2015-16" 159 10 = "2017-18"; 160 161 value statusf 162 1 = "Interviewed only" 163 2 = "Both interviewed and MEC examined"; 164 165 value sexf 166 1 = "Male" 167 2 = "Female"; 168 169 value race_ethf 170 1 = "Mexican American" 171 2 = "Other Hispanic" 172 3 = "Non-Hispanic White" 173 4 = "Non-Hispanic Black" 174 5 = "Other Race - Including Multi-Racial"; 175 176 run; 177 178 *APPLY THE FORMATS; 179 180 data work.data_dict_labeled; 181 set work.data_dict; 182 format 183 cyclecyclef. 184 statusstatusf. 185 sexsexf. 186 race_ethrace_ethf. 187 ; 188 run; 189 190 *****************************************************************************************; 191 *Create a combined long-format frequency table of multiple variables by survey cycle*; 192 *that include the NHANES cycle, name, values, and frequency counts for each variable *;*; 193 *****************************************************************************************; 194 195 196 *Step 1: Create a global macro variable (&vars) with the list of variables being processed; 197 %let vars = status sex race_eth; 198 199 200 *Step 2: Create an empty output table; 201 data all_freqs; 202 length cycle 8 variable $32 value $100 count 8; 203 stop; 204 run; 205 206 *****************************************************************************************; 207 *MACRO; 208 *Step 3: Define the macro to loop over variables; 209 %macro freq_by_cycle; 210 %local i vars; 211 212 *Step 4: Loop through the variable list; 213 %let i = 1; 214 %do %while(%scan(&vars, &i) ne ); 215 %let var = %scan(&vars, &i); 216 217 *Step 5: Create a frequency table; 218 proc freq data=work.data_dict_labeled noprint; 219 tables cycle * &vars / out=freq_temp(drop=percent); 220 run; 221 222 *Step 6: Reshape the output; 223 data freq_temp; 224 set freq_temp; 225 length variable $32 value $100; 226 variable = "&vars"; 227 value = vvaluex(symget("vars")); 228 keep cycle variable value count; 229 run; 230 231 *Step 7: Append the result; 232 proc append base=all_freqs data=freq_temp force; run; 233 234 *Step 8: Finish the loop; 235 %let i = %eval(&i + 1); 236 %end; 237 %mend freq_by_cycle; 238 *****************************************************************************************; 239 240 *Step 9: Run the macro; 241 %freq_by_cycle; 242 243 244 *EXPORT THE NEW DATASET; 245 %CreateDataDictionary(lib=work, data=data_dict_labeled, out=nhanes_dict); 246 ods excel file="/home/u58028236/myfolders/nhanes_dict.xlsx" options(sheet_name="Data Dictionary"); 247 proc print data=nhanes_dict noobs label; 248 run; 249 ods excel close; 250 251 252 253 254 proc export data=all_freqs 255 outfile="~/myfolders/freq_status.csv" 256 dbms=csv 257 replace; 258 run; 259 260 261 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 262 ODS HTML CLOSE; 263 &GRAPHTERM; ;*';*";*/;RUN;QUIT; 264 QUIT;RUN; 265 ODS HTML5 (ID=WEB) CLOSE; 266 267 FILENAME _GSFNAME; 268 DATA _NULL_; 269 RUN; 270 OPTIONS NOTES STIMER SOURCE SYNTAXCHECK;
From that LOG your EXPORT steps never ran because you confused SAS by having unbalanced quotes at some point. You see it in the first 10 lines of the SAS LOG.
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 2 TITLE; NOTE: The quoted string currently being processed has become more than 262 bytes long. You might have unbalanced quotation marks. 3 FOOTNOTE; 4 OPTIONS LOCALE=en_US DFLANG=LOCALE; 5 DATA _NULL_; 6 RUN; 7 OPTIONS VALIDVARNAME=ANY; 8 OPTIONS VALIDMEMNAME=EXTEND; 86 &GRAPHTERM; ;*';*";*/;RUN;QUIT; _________________ 49 NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space between a quoted string and the succeeding identifier is recommended. 9 FILENAME _HTMLOUT TEMP;
Try resetting the SAS session and resubmitting your code. Make sure that your code does not have unbalanced quotes.
Not only did you have an unmatched quote, there also was something which keeps the "magic statement"
*';*";*/;RUN;QUIT;
from working properly. This might be a %MACRO definition without a matching %MEND.
Restart your SAS session, and rerun your code step-by-step. A macro definition has to be treated as a single step in this regard.
Thanks, but I saw this trouble-shooting when I was Googling the issue before posting here. I did use the approach you suggest (right clicking on the desired folder, clicking on Properties, and then copying and pasting the contents of the Location box. I've also tried numerous locations in the Files (Home) folder on SAS, always using this approach. For these reasons, I don't think using an incorrect file path is the problem.
This message highlighted in red below indicates a very likely mismatched quote somewhere previous th code you show:
1 FILENAME _DATAOUT TEMP; 12 %LET SYSCC=0; 13 %LET _CLIENTAPP='SAS Studio'; 14 %LET _CLIENTAPPABREV=Studio; 15 %LET _CLIENTAPPVERSION=3.81; 16 %LET _CLIENTVERSION=3.81; NOTE: The quoted string currently being processed has become more than 262 bytes long. You might have unbalanced quotation marks. 17 %LET _CLIENTMODE=wip; 18 %LET _SASSERVERNAME=%BQUOTE(SASApp); 19 %LET _SASHOSTNAME=%BQUOTE(odaws01-usw2); 20 %LET _SASPROGRAMFILEHOST=%BQUOTE(odaws01-usw2); 21 %LET _CLIENTUSERID=%BQUOTE(u58028236); 22
And since none of those data steps show anything about observations or run time then they did not execute at all. Which would explain why nothing was exported. The repeated notes about the quoted string means that all the "code" is actually inside something that has been quoted and never sees an execute .
One other thing to look at is in your macro definition. Use of incline comments such as
*Step 5: Create a frequency table;
Can cause problems. Thin inline comment for macros is %*. Either this or the /* comment */ style of comment should be used inside of a macro definition.
From the documentation for /* Macro comment:
Comparisons
SAS comment statements of the form*commentary;
orcomment commentary;
are complete SAS statements. Consequently, they are processed by the tokenizer and macro facility and cannot contain semicolons or unmatched quotation marks.
Many thanks to everyone who responded to my question -- the responses were very helpful. I was able to make tweaks to my code based on them and now I am able to export Excel tables. I really appreciate the comments about interpreting the log and making inline comments. I don't often see this type of very useful information!
Statements comments in SAS macro definitions server a vital role in making your code understandable.
SAS has three types of comments:
* Statement comment ;
%* Macro comment ;
/* Block comment */
You can use statement comments to leave messages to the user of the macro because they will appear in the SAS log when MPRINT is turned on. That is because the macro processor treats them the same any other normal SAS statements, like a PROC PRINT or a RUN statement.
You can use macro comments to leave messages to the coder.
Use block comments when you need to comment out multiple statements or embed a comment in the middle of another statement.
But you need to make sure not to use unbalanced quotes in the statement comments. Just like you do not use unbalanced quotes in other statements. The only place where you can use unbalanced quotes safely is in block comments.
This is helpful. Two follow-up questions:
(1) When I am first learning new SAS language or techniques, I like to input lots of notes explaining what I've done and why. What type of inline comments would you recommend in this instance (in light of the comment that statement comments can sometimes cause problems).
(2) In collections of code (whether SAS, Stata, etc.), headers are often created using *'s, /'s, ;'s, etc. to help organize the code. For example, this is from a CDC tutorial using NHANES data:
************************************************************************************************;
* Stata code for replicating NCHS Data Brief No. 397 F&V Consumption Among Adults in the US, 2015-2018*;
*Note that all graphs are done in Excel after creating a table of results
************************************************************************************************;
I note that this example uses statement comments. Do you recommend a different way of doing this?
I generally use the /* comment */ style of comment unless there is a very specific reason to have the comment appear in the log. They work in both the macro and normal code. Plus the SAS enhanced editor supports making and removing them easy with the Ctrl / (comment an entire line) and Shift Ctrl / (remove line comment).
Caution: it is possible to accidentally nest comments with the editor and blocks of text and that can be a bad thing.
I also tend to separate comments to different lines than actual code to avoid the issues involving the editor line comment keys. But that may just be me.
One of the issues with the basic inline comment style of * some comment text; happens when the comment involves a single quote which might happen when saying something like "In the following code I'm looking to do X" or macro triggers like % or & .
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.