I'm trying to identify patients who are hospitalized based on a primary diagnosis that is included in certain groups of diagnoses. These groups are called PQIs. There are also exclusions that would exclude patients from these groups. Exclusions can be diagnoses or medical procedures, and they can come from any DX or proc variable. They don't have to be a primary diagnosis.   I have a file with medical claims for each patient, with 1 record per hospitalization. Each record has 25 DX vars and 25 proc vars (I'm only showing 3 of each here). The hospitalizations should be flagged independently of each other.   I have another file with a flag associated with each DX or proc code related to each PQI. So the first step is to compare the DX inclusion codes against the primary diagnosis variable in the claims file, and then flag any hospitalization that matches. The next step is to match the DX or proc exclusion codes against any of the 25 DX or proc vars in the claims file, and flag the matches.   I set up a hash to do this, based on similar work I did a few years ago. But it's not flagging correctly and I don't know why. This is what it should look like: Obs FLAG_INC_PQI5 FLAG_EXC_PQI5 FLAG_INC_PQI7 FLAG_EXC_PQI7 BENE_ID ADMSN_DT 1 1 1 0 0 12345 21223 2 0 0 0 0 12345 21339 3 0 0 0 1 23456 21253   data have; infile datalines dsd dlm=',' truncover; input BENE_ID ADMSN_DT date9. DGNS_1_CD $ DGNS_2_CD $ DGNS_3_CD $ SRGCL_PRCDR_1_CD $ SRGCL_PRCDR_2_CD $ SRGCL_PRCDR_3_CD $; datalines; 12345,08FEB2018,J410,E118,E125,04CL3ZZ,"","" 12345,04JUN2018,L110,E185,E120,08DG3ZZ,"","" 23456,10MAR2018,E185,L110,E120,04CL3ZZ,"","" ;RUN; data flags; infile datalines dsd dlm=',' truncover; input FLAG_INC_PQI5 FLAG_EXC_PQI5 FLAG_INC_PQI7 FLAG_EXC_PQI7 INC_DX_code $ EXC_DX_code $ EXC_proc_code $; datalines; 1,0,0,0,J410,.,. 0,1,0,0,.,E118,. 0,0,0,1,.,.,04CL3ZZ ;RUN; data want(keep=BENE_ID ADMSN_DT FLAG_:); LENGTH INC_DX_Code EXC_DX_Code EXC_proc_Code $ 8; if _n_=1 then do; if 0 then set flags; dcl hash h_inc_dx(dataset:"flags"); h_inc_dx.defineKey('INC_DX_Code'); h_inc_dx.defineData(all:'y'); h_inc_dx.defineDone(); dcl hash h_exc_dx(dataset:"flags"); h_exc_dx.defineKey('EXC_DX_Code'); h_exc_dx.defineData(all:'y'); h_exc_dx.defineDone(); dcl hash h_exc_proc (dataset:"flags"); h_exc_proc.defineKey('EXC_proc_Code'); h_exc_proc.defineData(all:'y'); h_exc_proc.defineDone(); end; call missing(of _all_); set have; /*Inclusion DX codes apply to Principal DX (DGNS_1_CD) only."*/ Array dx_inc_codes $ DGNS_1_CD; Array dx_exc_codes $ DGNS_1_CD DGNS_2_CD DGNS_3_CD; Array proc_exc_codes $ SRGCL_PRCDR_1_CD SRGCL_PRCDR_2_CD SRGCL_PRCDR_3_CD; array _a_dx_inc $ DGNS_1_CD; do _i=1 to dim(_a_dx_inc); if h_inc_dx.find(key:_a_dx_inc[_i])=0 then do; output; /*return;--The return statement writes a row to output as soon as it finds a matching flag and then stops further checks. But since there can be multiple flag matches per record, it should be commented out*/ end; end; array _a_dx_exc $ ADMTG_DGNS_CD DGNS_1_CD DGNS_2_CD DGNS_3_CD; do _i=1 to dim(_a_dx_exc); if h_exc_dx.find(key:_a_dx_exc[_i])=0 then do; output; /*return;*/ end; end; array _a_proc_exc $ SRGCL_PRCDR_1_CD SRGCL_PRCDR_2_CD SRGCL_PRCDR_3_CD; do _i=1 to dim(_a_proc_exc); if h_exc_proc.find(key:_a_proc_exc[_i])=0 then do; output; /*return;*/ end; end; /*Set up an array to recode missing values to 0*/ Array _arr(*) FLAG_:; Do i=1 to dim(_arr); If _arr(i)=. then _arr(i)=0; End; Drop i; RUN; ... 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