I'm working on a task that is a continuation of my previous post. But now I need to change the output table so that the percentages within each column (for each analysis variable) sum to 100%.   Below is sample data, and the code that I have so far. PUFFWGT is the person weight, while PUFF001 is a replicate weight to account for effects of the sampling/survey design. There are actually 100 replicate weights, but I only included 2 as an example. TM_group and ADRD_group are my subgroups, DEM_AGE is one of the analysis variables, and cohort_flag indicates cases that are included in the analysis. I'm only interested in cases where cohort_flag=1, but I can't delete cases where cohort_flag=0 because this is weighted sample data.       data have; infile datalines dsd dlm=',' truncover; input TM_group ADRD_group DEM_AGE cohort_flag PUFFWGT PUFF001-PUFF002; datalines; 0,0,2,1,2110.5357,676.0632354,3108.917787 0,1,3,1,918.4676314,1106.627216,11398.01208 0,0,3,1,2758.096955,4735.215718,1418.690422 0,1,2,1,2456.208457,918.6245816,4548.556261 0,1,2,1,616.4292264,1033.661611,1213.613946 0,0,2,1,3673.275956,6316.504889,1704.047563 0,1,3,1,1102.850296,304.9154776,904.2769054 0,0,3,1,2714.610724,877.3159168,993.7824266 0,1,2,1,20090.52479,6085.947656,7590.092351 1,1,3,1,9098.780523,3498.802425,1335.317433 1,1,2,0,7196.92957,11977.46854,1240.066816 1,1,3,1,842.3146743,1255.501335,4314.886534 1,0,2,0,13516.51008,4775.164303,1106.604846 1,0,2,0,4460.828162,1083.99043,943.9937287 1,0,3,0,4818.845124,9286.932256,8197.376118 1,0,2,1,2848.023551,4739.321046,1113.329766 1,.,3,0,3050.820193,805.9607077,761.0495075 1,.,2,1,4256.565135,1176.233283,1775.665732 1,1,2,1,5211.416525,1570.093954,291.3352611 1,1,3,0,4181.422692,8242.459467,2573.543707 1,1,3,1,3179.943422,4688.70721,6518.281019 .,.,2,1,3451.708714,4256.625072,1167.477312 .,.,3,1,3018.745468,4729.150346,1565.75873 .,.,3,1,4098.775,1486.742512,1283.25 .,.,3,1,3483.708151,1098.980881,1349.598 ;RUN; /*Section 0.5 -- Sort analysis file on BY variables, as required by PROC SURVEYFREQ*/ PROC SORT data=have; BY cohort_flag; RUN; /*** SECTION 1 -- Examine weighted freqs for each group ***/ /*Section 1.1.2 -- Generate weighted freqs*/ title1 "Section 1.1.2 -- Weighted freqs - both person and sample weights V&version."; PROC SURVEYFREQ data=have missing; WEIGHT PUFFWGT; REPWEIGHTS PUFF001-PUFF002; BY cohort_flag; TABLES TM_group*ADRD_group*DEM_AGE / nosparse nowt ; RUN;   Here is one of the output tables, where cohort_flag=1. Note that when ADRD_group=1, DEM_AGE has freqs where '2'=3 and '3'=2, for a total of 5. That's correct, but the percents are roughly 63% and 5%, when they should be 60% and 40% so that they sum to 100%. The total row should read "Total     5     100"     Table of ADRD_group by DEM_AGE Controlling for TM_group=0 ADRD_group DEM_AGE Frequency Percent Std Err of Percent 0 2 2 15.8717 11.23   3 2 15.018 9.1592   Total 4 30.8897 19.6118 1 2 3 63.5635 25.122   3 2 5.5468 22.5428   Total 5 69.1103 19.6118 Total 2 5 79.4352 18.873   3 4 20.5648 18.873   Total 9 100       ... View more

I created a yes/no/missing format. It works when I apply it to a variable that I constructed in my syntax, but not to variables that were already present in the data file. Could the existing variables already have a format that is being retained? And if so, how do I override it?   The syntax below will produce a freq table for Any_cancer with 1:Yes, 2: No, Inapplicable/Missing, in that order.  This is a constructed variable, and the output is being put into a table. So having it in the correct order is important.   The table for HLT_OCSTROKE shows 2, 1:Yes, Inapplicable/Missing, in that order. This is an existing variable in the data, and there are about 10 more that are doing the same thing.   I would add sample data, but I can't see how it would fail to run properly with new data. The only thing that makes sense is that the formatting is retained from the original data.     LIBNAME temp "C:\SAStemp"; proc format library=temp; value yesfmt_r 1='1:Yes' 2='2:No' .='Inapplicable/Missing' .R='Inapplicable/Missing' .D='Inapplicable/Missing' .N='Inapplicable/Missing'; RUN; DATA temp.want; SET temp.have; FORMAT Any_cancer YESFMT_r. HLT_OCSTROKE YESFMT_r.; RUN; PROC FREQ data=temp.want; TABLES Any_cancer HLT_OCSTROKE / missing; RUN;       ... View more

If you've seen any of my recent posts, you know that I've been struggling with this for a while now. My supervisor keeps telling me it's easy and it should have only taken a day. What I'm finding is that it's fairly easy to generate the numbers, but very time-consuming and tedious to rearrange them into a summary table. Proc Report seems to help a lot, but there is still a lot of data prep to get to a dataset that can be fed into Proc Report.   Anyway, as the title says, I'm trying to create a summary table. Below is sample data, and the code that I have so far. PUFFWGT is the person weight, while PUFF001 is a replicate weight to account for effects of the sampling/survey design. There are actually 100 replicate weights, but I only included 2 as an example.   Also below is an example table that shows the desired layout. This table was created with the TableN macro, which works great, but unfortunately doesn't work with weights. I'm not too concerned with labels right now, I just need the correct numbers in the correct place. There are a few differences between the Proc Report output and the desired table: --There is no column for missing. --There is no column for Total.  In each row, this should be the sum of the Missing, no ADRD, and ADRD groups. The Total row has the correct n, but not the correct percentage. --Once the Total column is created, I can drop the Frequency columns. --The percentages in () should be column totals for each variable. So the percentage of DEM_AGE=1 + DEM_AGE=2 should equal 100% --Need to add a column for the p-values from the chisq file.   data have; infile datalines dsd dlm=',' truncover; input TM_group ADRD_group DEM_AGE DEM_SEX PUFFWGT PUFF001-PUFF002; datalines; 0,0,1,1,2110.5357,676.0632354,3108.917787 0,1,1,1,918.4676314,1106.627216,11398.01208 0,0,1,1,2758.096955,4735.215718,1418.690422 0,1,1,1,2456.208457,918.6245816,4548.556261 0,1,1,1,616.4292264,1033.661611,1213.613946 0,0,1,1,3673.275956,6316.504889,1704.047563 0,1,1,1,1102.850296,304.9154776,904.2769054 0,0,1,2,2714.610724,877.3159168,993.7824266 0,1,2,2,20090.52479,6085.947656,7590.092351 1,1,1,2,9098.780523,3498.802425,1335.317433 1,1,1,2,7196.92957,11977.46854,1240.066816 1,1,1,1,842.3146743,1255.501335,4314.886534 1,0,2,2,13516.51008,4775.164303,1106.604846 1,0,1,2,4460.828162,1083.99043,943.9937287 1,0,1,2,4818.845124,9286.932256,8197.376118 1,0,1,1,2848.023551,4739.321046,1113.329766 1,.,1,2,3050.820193,805.9607077,761.0495075 1,.,1,1,4256.565135,1176.233283,1775.665732 1,1,1,1,5211.416525,1570.093954,291.3352611 1,1,1,2,4181.422692,8242.459467,2573.543707 1,1,1,1,3179.943422,4688.70721,6518.281019 .,.,1,1,3451.708714,4256.625072,1167.477312 .,.,1,1,3018.745468,4729.150346,1565.75873 .,.,1,2,4098.775,1486.742512,1283.25 .,.,1,1,3483.708151,1098.980881,1349.598 ;RUN; /*Sort analysis file on BY variables, as required by PROC SURVEYFREQ*/ PROC SORT data=have; BY TM_group ADRD_group; RUN; /*** SECTION 1 -- Examine weighted freqs for each group ***/ /*Section 1.1 -- Weighted freqs - both person and sample weights*/ /*Section 1.1.1 -- Use ODS to save results into a file*/ ods output crosstabs = temp.freqs_raw_t1 chisq = temp.chisq_raw_t1; /*Section 1.1.2 -- Generate weighted freqs*/ title1 "Section 1.1.2 -- Weighted freqs - both person and sample weights V&version."; PROC SURVEYFREQ data=have VARMETHOD=BRR missing; WEIGHT PUFFWGT; REPWEIGHTS PUFF001-PUFF002; BY TM_group; TABLES DEM_AGE*ADRD_group DEM_SEX*ADRD_group / rschisq; RUN; /*** SECTION 2 -- Reorganize ODS output tables ***/ /*Section 2.1 -- freqs table*/ /*Keep needed variables and use macro to populate Value_Labels with proper values*/ DATA temp.freqs_t1_t; SET temp.freqs_raw_t1; DROP WgtFreq /*Frequency*/ StdDev StdErr _SkipLine; /*Create Value_Labels column and populate it with labels from each variable*/ Value_Labels = " "; %macro val_labs (val_labs); IF Table = "Table &val_labs. * ADRD_group" THEN Value_Labels = F_&val_labs.; DROP F_&val_labs. &val_labs.; %mend; %val_labs(DEM_AGE) %val_labs(DEM_SEX) /*Reformat and combine weighted count and percent columns*/ PERCENT_RD = ROUND(Percent,.01); Freq_percent = " "; Freq_percent = COMPRESS(Frequency||"("||put(PERCENT_RD,8.2)||"%"||")"); /*IF Frequency <10 THEN Freq_percent = "<10"*/; DROP /*Frequency*/ Percent PERCENT_RD; /*Retain Frequency because it is needed for the Total column*/ RUN; DATA temp.chisq_2nd_t1; SET temp.chisq_raw_t1; IF Label1 ^= "Pr > ChiSq" THEN delete; RUN; /*REVISE THIS SECTION TO INCLUDE MISSING COLUMN*/ /*Section 2.1.1 -- Put columns in correct order*/ PROC REPORT data=temp.freqs_t1_t; COLUMNS table Value_Labels TM_group, ADRD_group,(Freq_percent Frequency); DEFINE table / group; DEFINE value_labels / group; DEFINE TM_group / across; DEFINE ADRD_group /across; RUN; /*Section 2.2 -- chisq table*/ /*Drop unneeded variables, delete results for missing cases, and only keep Rao-Scott chisq p-value rows*/ DATA temp.chisq_t1_t; SET temp.chisq_raw_t1; DROP Name1 nValue1; IF TM_group = . THEN delete; /*IF ADRD_group = . THEN delete;*/ IF Label1 ^= "Pr > ChiSq" THEN delete; RUN;     MA_group   TM_group   Missing no ADRD ADRD Total P-value   Missing no ADRD ADRD Total P-value (N=14) (N=21836) (N=1232) (N=23082) (N=17) (N=29642) (N=1475) (N=31134) Age group, n (%)         <.0001 1           <.0001 1 2:Age Group [65,75) 4 (28.6%) 8701 (39.8%) 179 (14.5%) 8884 (38.5%)     5 (29.4%) 12504 (42.2%) 215 (14.6%) 12724 (40.9%)   3:Age Group >=75 10 (71.4%) 13135 (60.2%) 1053 (85.5%) 14198 (61.5%)     12 (70.6%) 17138 (57.8%) 1260 (85.4%) 18410 (59.1%)                           Gender, n (%)         <.0001 1           0.00751 1:Male 6 (42.9%) 9479 (43.4%) 453 (36.8%) 9938 (43.1%)     6 (35.3%) 13623 (46.0%) 619 (42.0%) 14248 (45.8%)   2:Female 8 (57.1%) 12357 (56.6%) 779 (63.2%) 13144 (56.9%)     11 (64.7%) 16019 (54.0%) 856 (58.0%) 16886 (54.2%)       ... View more

As I detailed in this post I'm trying to make a "Table 1" summary table and another additional table of descriptive statistics for dozens of variables. Generating the proper results is relatively easy. But rearranging those results for dozens of variables and putting them into a well-organized summary table takes a lot of code. The TableN macro does this extremely well, but it doesn't have a provision to add weights.   Before I go any further, has anyone else ever run into this issue? I'm hoping there may be a modified version of the macro that can include weights.   First and foremost, I need to add the person weight variable PUFFWGT. I believe all of the procs used in the macro (such as PROC MEANS, PROC FREQ, PROC UNIVARIATE, etc.) can include a WEIGHT statement for the person weight.   For simplicity sake, I'm attempting to hardcode PUFFWGT into these procedures. However, I'm discovering that the macro takes the input dataset, extracts key variables, and divides them into multiple files that each have a standard format that is specific to the macro. The datasets to which the Procs are actually applied no longer have the weight variables or any of the original variable names. The varnames and labels are presumably added back in at the end. So I can't just add the weight statement, because the weight variable is no longer there. ... View more

As I detailed in this post I'm trying to make a "Table 1" summary table and another additional table of descriptive statistics for dozens of variables. Generating the proper results is relatively easy. But rearranging those results for dozens of variables and putting them into a well-organized summary table takes a lot of code. The TableN macro does this extremely well, but it doesn't have a provision to add weights. Likewise, the code I wrote for the secondary table uses Proc Ttest, and there is no "SURVEY" equivalent (like Proc Surveyfreq is the equivalent to Proc Freq). Proc Ttest can use a person weight, but not sampling weights.   I'm supposed to have a meeting this afternoon to review the results. At this point, we just need initial results to start developing a theoretical model. So what kind of error will these results have? As I understand it, the standard error will be underestimated if weights are not used. As a result, some findings may appear to be significant but really aren't. Is that correct, and are there any other issues I should be aware of ? ... View more

I have very little experience working with weights, so please correct me if my understanding is wrong.   I'm trying to create a summary table of unadjusted rates of quality of care between the TM and MA groups. I was able to produce a table with Proc Ttest and ODS. However, the survey uses a complex design. I need to add a weight variable and, it appears, replicate weight variables. Unfortunately, Proc Ttest can accommodate a weight variable but not replicate weights.   Just to experiment, I tried running Proc Ttest with the weight variable, and the sig score for the variables improved. That confuses me, because the study documentation says "To permit the calculation of random errors due to sampling, a series of replicate weights were computed. Unless the complex nature is taken into account, estimates of the variance of a survey statistic may be biased downward." In other words, not using weights means underestimating the variance. And if the true variance is actually higher, shouldn't that reduce the significance level? One particular variable I looked at has a probt score of 0.0158 when unweighted, and 0.0025 when weighted.   Based on what I found in the study documentation, I'm trying to use Proc Surveyfreq instead. However, this is confusing me as well.  The Pr > ChiSq score is now <.0001 for every variable, even those that were not significant when I used Proc Ttest.   Here is the code, with sample data and Proc TTest commented out. I'm only including 1 of the replicate weights here, but there are actually 100 of them: data have; infile datalines dsd dlm=',' truncover; input ACC_HCTROUBL_r ACC_HCDELAY_r ADRD_group TM_group PUFFWGT PUFF001; datalines; 3,1,1,0,1310.792231,1957.576268 2,1,1,1,10621.60998,18588.46812 3,2,1,1,3042.093381,5484.728615 3,2,1,1,3166.358963,5497.289892 3,2,1,0,1481.272986,432.6313548 2,2,1,1,6147.605583,9371.965632 2,1,1,1,14001.79093,16689.25322 3,1,1,1,2035.685768,530.211881 2,1,1,1,6356.258972,1899.874476 3,2,1,0,1487.104781,2018.636444 2,1,1,0,5002.553584,1364.125425 2,2,1,1,2493.79145,4039.542597 3,2,1,0,2260.257377,3495.675613 2,2,0,1,9358.048737,2835.543292 3,2,1,1,2978.506348,4932.378916 3,2,1,1,2794.906054,5118.430973 3,1,1,0,1663.418821,519.7549258 3,2,1,0,2083.459361,3067.105973 2,1,1,0,5106.785048,8672.202644 3,1,1,1,3447.574748,854.6276748 3,2,1,1,2819.233426,899.849234 3,2,1,0,4067.38684,6463.15598 3,2,1,1,1249.96647,2053.666234 3,2,1,1,1730.237908,3058.307502 3,2,1,1,4932.936202,1479.55826 ; RUN; /*PROC TTEST plots=none data=have; CLASS TM_group; VAR ACC_HCTROUBL_r ACC_HCDELAY_r; WEIGHT PUFFWGT; REPWEIGHT PUFF001; /*REPWEIGHT PUFF001-PUFF100;*/ RUN;*/ PROC SURVEYFREQ data=have VARMETHOD = brr (fay=.30); TABLE ACC_HCTROUBL_r ACC_HCDELAY_r * TM_group / row chisq lrchisq; WEIGHT PUFFWGT; REPWEIGHT PUFF001; /*REPWEIGHT PUFF001-PUFF100;*/ WHERE ADRD_group ^= 1; RUN; ... View more