I've downloaded the 2015-2020 all-sites ("overall") BRFSS datasets; I've worked with individual state-level datasets before to get general prevalence estimates for--say--reported depression.   I'm trying to get US/all-sites estimates for some measures not found on the CDC's online prevalence tool. I know they report median values.   What is the easiest way to go about this in SAS without getting stratified prevalence estimates for each state and then pasting it into Excel and finding the median from there? I want to get a median trend year-over-year. I was thinking of running the initial prevalence tables, then passing those to an SQL table and then finding the median from there?    So far I've been running one single year's dataset and it's taken like 40 minutes and hasn't finished--not sure how I can speed that up. But any suggestions for faster processing and less coding mistakes (and thus having to go back and fix the code and re-run) would be super appreciated.  ... View more

I'm working with qualitative data, where a variable COMMENT represents a string of text written in by a study participant. Some comments aren't in full sentences, but some have several sentences.    I've figured out how to split the string at any period it finds into a separate row & retain an ID value for that (so I can manually match up the comment "pieces" to the original full comment if needed):   do i=1 by 1 while(scan(COMMENT,i,'.')^=' '); new=scan(COMMENT,i,'.'); retain ID; output; end; While looking through my first run of this code, I realized there is a problem: some comments will include things like "Mr." or a decimal like "12.1".    What's the easiest way to add a layer to this code that will split the string only at the end of an actual sentence (delimited by a period)? ... View more

I want to find a way to flag or otherwise ID the ranges of 95% CLs for a series of datasets I have.  Obs FPL RowPercent RowLowerCL RowUpperCL 1 1 67.4 63.5 71.4 2 2 68.0 62.5 73.6 3 3 63.8 56.2 71.4 4 4 51.1 42.0 60.3 These datasets are outputs from previous crosstabulations. I run Chi-square tests for these crosstabulations as well, but I want a visual way to ID and get a sense of which CLs overlap with all the others (not just compare FPL=1-2, 2-3, etc.).   I've come across various approaches to ID consecutive data ranges that overlap, largely with first./last. and retain statements, but they don't work for my situation. I think a matrix might be best to concisely ID the unique pairs, but I'm not sure how to approach that.  ... View more

I'm using complex survey data to assess various outcomes, stratified by various demographic variables. I'm running Chi-Square tests to assess IDed significant differences, but I want an easier way to find 95% CLs that overlap within those groups (as opposed to scrolling through 100s of output tables and writing each one down), without any additional testing (at this point).   I've output the crosstabulated tables for all variables. I've formatted each of these mini datasets. I want to code something that basically flags the CLS that overlap between groups; I realize this is a bit more complicated than I'm making it out to be. But how can I create a code that says "for each level of this categorical variable, compare the lower and upper CLs to find overlaps". The demographic variables have different numbers of categories, so I'd like to use a macro and Proc Freq NLEVELS to pass the number of levels (and thus automate it).    ... View more

I'm using complex survey data to run proc surveyfreq with several table statements, the variables of which are stratified by several demographic variables. I'm also running Chi-square tests on everything.    I want to only print the tables that have Chi-square p-values <0.05--is this possible? I'm not sure how to use ODS to select what I want.  ... View more

I'm new to SAS macros, but have put some together using online help and other resources to (mostly) automate a SAS program I need to regularly run. My current code starts off by setting up a dataset using survey data (establishing weight and strata vars, recoding, etc.). Then I have several separate macro programs that perform separate tasks meant to output various pieces I use later for putting into the report's Word document. These macros are solely for iterative/space-saving purposes to run a certain procedure once for each of the many variables I tell it to use.   The data step and the subsequent proc steps (within their macros) all run perfectly when I run them one at a time. The issue I'm having is that running the entire code itself either results in skipped output from the macros while other output is produced, the "most recent" macro to run will output its data under the name of the file that was supposed to be produced before it, or this same thing will happen with two+ output files--the output will be perfect, but the file names will be reversed.    I understand that macros are compiled first in SAS code, so I'm not sure if its an issue with invocation or something entirely different, but I'd appreciate anything I've overlooked. An example of my ugly macros are below; I'm not able to share actual data due to its sensitivity:   * Set macro list of vars; %let varnames= RACE2 age BMI FPL WIC Division Urban Married edu insur intended any_method effective pre_vist PNC_1TRM checkup flu FLUPREG DDS_Cln pre_diet pre_exer mvitamin vitamin sleep sleep_alone approved_surface BF5EVER wk_length; ods html close; ODS NOPROCTITLE; %macro yearly; ods excel file="C:\Exports_&startyr-&endyr..xlsx"; ods excel options(sheet_interval="none" sheet_name="Original_5 Year"); %do i=1 %to %sysfunc(countw(&varnames)); %let thisname=%scan(&varnames,&i,%str( )); *PROC FREQ through list; ODS exclude Summary; ODS exclude VarianceEstimation; ODS select CrossTabs; ODS output CrossTabs=year_&thisname; Proc surveyfreq data=rollrpt3 total=TOTALS NOMCAR; Weight weightvar; Strata stratvar; Tables year*(&thisname) / row cl nofreq nostd nototal; Run; ODS Tagsets.ExcelXP CLOSE; %end; %mend; %yearly /***************************************** Only Data Exports for Excel Charts *****************************************/ %let charnames= Insur pre_vist DDS_Cln MOM_BMIG_QX_REV vitamin smk63B_A smk63L_A smk6NW_A violence sleep sleep_alone approved_surface wk_length; ** Formatted Reports to output; %macro charts; *EXCEL output, once everything is printed; ods excel file="C:\For_Charts.xlsx"; ods excel options(sheet_interval="none" sheet_name="For Charts"); %do i=1 %to %sysfunc(countw(&charnames)); %let thisname=%scan(&charnames,&i,%str( )); TITLE; Proc report data=year_&thisname nowd CENTER WRAP; Column &thisname Year,(RowPercent) dummyvar; Define &thisname / order=internal group flow; Define Year / across; Define RowPercent / "%" display; /* Dummy var for alignment */ Define dummyvar / computed noprint ; compute dummyvar; dummyvar = 1; endcomp; Run; ODS Tagsets.ExcelXP CLOSE; %end; %mend; %charts ... View more

I have a SAS code that, from crosstab tables, generates mini datasets that are then run through Proc Report to generate individual report tables to show prevalence estimates & 95% CLs from survey items across 5 years.   There are about 55 individual variables, all with 2+ response categories.    Is it possible to get these all into one single table, so that each variable is still its own group, separated by a bold horizontal line?   ** List of ~55 variables code iterates through; %let crossnames= bpinsur dpinsur ppinsur . . . . . ; ODS HTML; %macro dothisb; TITLE; %do i=1 %to %sysfunc(countw(&crossnames)); %let thisname=%scan(&crossnames,&i,%str( )); ** Create Simplified crosstab datasets; Data year3_&thisname; Set year2_&thisname; length newvar $20; length CLS $12; newvar=cat(put(Row_Percent, 4.1),' [',put(Lower_CL, 4.1),' ',put(Upper_CL, 4.1),']'); CLS=cat(' [',put(Lower_CL, 4.1),'-',put(Upper_CL, 4.1),']'); DROP RowPercent RowLowerCL RowUpperCL ; Label newvar="%[95%CL]"; Label CLS="[95%CL]"; Run; ** PROC REPORT; Proc report data=year3_&thisname out=report_&thisname nowd CENTER WRAP ; Column &thisname YY4_DOB,(Row_Percent CLS) dummyvar; Define &thisname / order=internal group flow; Define yy4_DOB / "Year" across; Define Row_Percent / "%" display; Define CLS / display ; Define dummyvar / computed noprint ; compute dummyvar; dummyvar = 1; endcomp; Run; %end; ODS rtf CLOSE; %mend; %dothisb                                                                    Year   2016 2017 2018 2019 2020 Reported Insurance During % [95%CL] % [95%CL] % [95%CL] % [95%CL] % [95%CL] None 3.2 [1.2 - 4.2]                 Private                     Medicaid                                                                                            Year   2016 2017 2018 2019 2020 Reported Insurance After % [95%CL] % [95%CL] % [95%CL] % [95%CL] % [95%CL] None                     Private                     Medicaid                     ... View more

I have the following code: Data year3_insur; Set year2_insur; length newvar $20; newvar=cat(put(Row_Percent, 4.1),' [',put(Lower_CL, 4.1),'-',put(Upper_CL, 4.1),']'); DROP RowPercent RowLowerCL RowUpperCL ; Label newvar="% [95% CL]"; Run; It concatenates 3 numeric variables into a single string that is put into a Proc Report; the string looks like: 90.0 [88.8-99.99].   I want to bold the 90.0 numbers (Row_Percent variable). I've tried using the ODS escapechar feature, but can't get it to work. I'm currently writing it as:   newvar=cat(put(Row_Percent, 4.1)^{style [font_weight=bold],' [',put(Lower_CL, 4.1),'-',put(Upper_CL, 4.1),']'); What am I doing wrong and how can I make this work?  ... View more

I have three character variables (percent, lower CL, upper CL) that I pulled from a Crosstab table. They are numeric (length = 8, format = 7.4), and when I print out the table, it gives values that have (as expected) 4 decimal places.    I'm trying to change that to 1 decimal place, retaining any trailing zeros so when I run a proc report, all the values are visually even with each other & easier to read.    I've tried a variety of approaches that aren't quite working; I have found something (even if it doesn't make sense...) that gets close to what I want using the code below; the code iterates through a list of variables I previously defined so I don't have to run the code for each variable one at a time. It then concatenates and formats the values for the proc report, printing it out to a Word document.   ODS HTML; %macro dothisb; ** WORD output, once everything is printed; ods rtf file="\PATHWAY\Report_Tables..rtf" startpage=no noKEEPN; options nodate nonumber orientation=portrait; TITLE; %do i=1 %to %sysfunc(countw(&crossnames)); %let thisname=%scan(&crossnames,&i,%str( )); ** Create Simplified crosstabs from earlier proc surveyfreq; Data year2_&thisname; Set year_&thisname; DROP Table intended F_: WgtFreq Percent LowerCL UpperCL _SkipLine; /* CHAR --> NUMERIC */ Row_Percent=input(RowPercent, 4.1); Lower_CL=input(RowLowerCL, 4.1); Upper_CL=input(RowUpperCL, 4.1); Run; ** Format Simplified tables-- Concatenate % and 95% CLs; Data year3_&thisname; Set year2_&thisname; /* Percent formatting */ Percent=(Row_Percent||"% "); /* Confidence Interval Formatting */ Confidence=("("||Lower_CL||"-"||Upper_CL||")"); DROP RowLowerCL RowUpperCL ; Label Percent="%"; Label Confidence="95% CL"; Label combined="% (95% CL)"; Run; ** PROC REPORT; Proc report data=year3_&thisname out=report_&thisname nowd STYLE(report) = [rules=none frame=hsides background=white cellspacing=0 cellpadding=.05] STYLE(header) = [foreground=black background=white cellheight=.40in borderbottomwidth=2.0pt borderbottomcolor=black bordertopwidth=.5pt bordertopcolor=white borderleftwidth=.5pt borderleftcolor=white borderrightwidth=.5pt borderrightcolor=white] ; Column &thisname YY4_DOB,(Percent Confidence) dummyvar; Define &thisname / order=internal group style(column) = {vjust=top just=left borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt paddingright=.05in}; Define yy4_DOB / "Year" across style(column) = {vjust=top just=Middle borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt paddingleft=.05in}; Define Percent / display style(column) = {cellheight=.40in cellwidth=0.55in vjust=top just=left borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt paddingright=.05in}; Define Confidence / display style(column) = {cellheight=.40in cellwidth=0.75in vjust=top just=middle borderleftcolor=white borderleftwidth=.5pt borderrightcolor=black borderrightwidth=.5pt paddingleft=.05in}; Define dummyvar / computed noprint ; compute dummyvar; dummyvar = 1; endcomp; Run; %end; ODS rtf CLOSE; %mend; %dothisb *Invoke; %dothisb However, I have some problems. The original values look like this: Year dpinsur RowPercent RowLowerCL RowUpperCL 2016 None 1.7935 0.2168 3.3702 2016 Private 58.3730 53.3669 63.3792 2016 Public 39.8334 34.8675 44.7994 2017 None 1.8858 0.4645 3.3071 2017 Private 59.7053 55.0038 64.4067 2017 Public 38.4089 33.7411 43.0767 2018 None 0.2916 0.0000 0.6793 2018 Private 65.5822 60.9709 70.1935 2018 Public 34.1263 29.5189 38.7336 2019 None 1.4170 0.0000 2.9249 2019 Private 53.5578 47.5044 59.6111 2019 Public 45.0252 38.9781 51.0723 2020 None 0.6038 0.0000 1.3675 2020 Private 61.9009 56.0220 67.7798 2020 Public 37.4953 31.6267 43.3639   And my proc report sometimes gives values like this, where some values are not being rounded correctly or the zeros from the 95% Confidence limits are not being carried over like I want.    Year   2016 2017 2018 2019 2020  Insurance Status % 95% CL % 95% CL % 95% CL % 95% CL % 95% CL None 1.79% (0.21-3.37) 1.88% ( 0- 3.3) 0.29% ( .-0.67) 1.41% ( .-2.92) 0.6% ( .-1.36) Private 58.3% (53.3-63.3) 59.7% (55-64) 65.5% (60-70.1) 53.5% (47.5-59.6) 61% (56-67.7) Public 39.8% (34.8-44.7) 38.4% (33.7-43) 34.1% (29.5-38.7) 45% (38-51) 37.4% (31.6-43.3)   Like I've said, I've tried a variety of ways to work around this, but haven't found one that gets it totally right. Any help would be greatly appreciated; I've been tinkering with this for hours.  ... View more

I a table:   Year   2016 2017 2018 2019 2020 Region Row_Percent Row_Percent Row_Percent Row_Percent Row_Percent East 31.65 30.15 53.66 58.37 50.8 Middle 44.39 46.45 36.83 31.2 40.45 West 23.95 23.39 9.51 10.44 8.75   It's produced using this code: Proc report data=year3_region nowd; Column region YY4_DOB,Row_Percent dummyvar; Define region / group; Define yy4_DOB / across; Define Row_Percent / display ; ** Dummy Var to help with formatting; Define dummyvar / computed noprint ; compute dummyvar; dummyvar = 1; endcomp; Run; The Row_percent comes from a previous crosstab output I retrieved.    If possible, Iwant to add a new column at the end that represents the average of that row_percent across the 5-year period for each region. I've tried a few different examples I've found online, but none of them have worked. What's the best way to do this? ... View more

I'm using ODS RTF to output Proc Reports to a Word document. The following is my code:   ods rtf file='C:\Annual Summary Reports_2018-forward\Rolling Reports\AUTOLIB\Tables.rtf' noKEEPN; options nodate nonumber orientation=portrait ; ** PROC REPORT work; Proc report data=year3_region out=report_&thisname nowd STYLE(report) = [rules=none frame=hsides background=white cellspacing=0 cellpadding=.20] STYLE(header) = [foreground=black background=white cellheight=.23in borderbottomwidth=2.0pt borderbottomcolor=black bordertopwidth=.5pt bordertopcolor=white borderleftwidth=.5pt borderleftcolor=white borderrightwidth=.5pt borderrightcolor=white] ; Column region YY4_DOB,Percent dummyvar; Define region / group style(column) = {cellheight=.23in cellwidth=1.0in vjust=bottom just=left borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt paddingright=.05in}; Define yy4_DOB / across style(column) = {cellheight=.23in cellwidth=1.25in vjust=bottom just=Middle borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt paddingleft=.05in}; Define Percent / display style(column) = {cellheight=.23in cellwidth=1.25in vjust=bottom just=left borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt paddingleft=.05in}; Define dummyvar / computed noprint ; compute dummyvar; dummyvar = 1; endcomp; Run; ODS rtf CLOSE;     This causes the following type of output. This is largely what I'm after, but I want the 2nd and 3rd lines to be less bold, and I can't figure out how to get it to display all the estimates without having all that space--this does not show up in the SAS output itself. Those estimates are a the result of a concatenation of 3 other variables in a previous data step:       Percent=Row_Percent || "%(" || Lower_CL || ", " || Upper_CL || ")" ;   How do I get rid of the extra spaces/wrapping AND how do I get rid of that "The SAS System" at the top? ... View more

I have a dataset table that I want to ultimately transpose:    Year PCP_Visit_past_year n Row_Percent Lower_CL Upper_CL 2016 Yes 628 84.6800 81.2606 88.0995 2016 No 110 14.5566 11.1867 17.9265 2016 Never vist 7 0.7634 0.0458 1.4809 2017 Yes 805 87.8194 84.9743 90.6645 2017 No 108 11.8672 9.0415 14.6928 2017 Never vist 4 0.3135 0.0000 0.6998 2018 Yes 583 86.1388 82.9866 89.2909 2018 No 82 13.2549 10.1490 16.3609 2018 Never vist 4 0.6063 0.0000 1.2480 2019 Yes 537 84.6572 80.5744 88.7401 2019 No 100 14.9445 10.8877 19.0013 2019 Never vist 6 0.3983 0.0000 0.9672 2020 Yes 492 84.5874 80.6830 88.4919 2020 No 94 14.3637 10.5594 18.1679 2020 Never vist 8 1.0489 0.0061 2.0916   I'm going to drop the frequency first, then I plan to use Excel to concatenate Row_Percent with the lower/upper CL --> % (95% CL) <-- and finally change this around to show: PCP_Visit_past_year 2016 2017 2018 2019 2020 Yes % (95% CL) % (95% CL) % (95% CL) % (95% CL) % (95% CL) No % (95% CL) .. .. .. .. Never vist % (95% CL) .. .. .. ..   But being the lazy person I am, there are probably 2-3 dozen tables I have to do this for--so how do I use SAS to make this easier? ... View more