I've assembled some frequency tables into a single file that I am now trying to organize into a table. I'm trying to use Proc Transpose, but all the examples I'm finding have at least 3 variables. My file only has 2 variables: Group COUNT_ROWPERCENT American_Ind_pt 76(0.50%) Asian_pt 635(4.15%) Black_pt 2176(14.21%) Hispanic_pt 1098(7.17%) White_pt 9702(63.36%) missing_pt 1486(9.70%) multi_race_pt 139(0.91%)   Here is my code: PROC TRANSPOSE data=HAVE out=WANT; BY group; ID group; VAR COUNT_ROWPERCENT; RUN; Which produces this:   Group _NAME_ American_Ind_pt Asian_pt Black_pt Hispanic_pt White_pt missing_pt multi_race_pt American_Ind_pt COUNT_ROWPERCENT 76(0.50%)             Asian_pt COUNT_ROWPERCENT   635(4.15%)           Black_pt COUNT_ROWPERCENT     2176(14.21%)         Hispanic_pt COUNT_ROWPERCENT       1098(7.17%)       White_pt COUNT_ROWPERCENT         9702(63.36%)     missing_pt COUNT_ROWPERCENT           1486(9.70%)   multi_race_pt COUNT_ROWPERCENT             139(0.91%)                     When I actually want this: American_Ind_pt Asian_pt Black_pt Hispanic_pt White_pt missing_pt multi_race_pt 76(0.50%) 635(4.15%) 2176(14.21%) 1098(7.17%) 9702(63.36%) 1486(9.70%) 139(0.91%)     ... View more

I'm calculating odds ratios between two binary (yes/no) variables. At some point in the process, the missing values for one of the variables were recoded to -1 and was left this way when the odds ratios were run. Fearing this may lead to inaccurate results, I recoded the -1's back to missing, ran frequencies to verify all -1's were now missing, and reran the odds ratios. The results didn't change at all.   After thinking about it, I believe the explanation is that the value of -1 would affect stats like mean and standard deviation. But for odds ratios, these variables are being treated as categorical rather than continuous because there are only 3 levels. Whether the missing cases are assigned a category ("-1") or just left as missing doesn't affect the odds for the "0" and "1" categories. Is my thinking correct, or is there something else going on here? ... View more

I have a list of diagnosis codes, and I want to flag if any of those codes exist anywhere in 15 different diagnosis variables. I created a series of IF-THEN statements, but I have many such lists of DX codes and an array is a more efficient approach. However, the array is only flagging a very small percentage of cases that should be flagged (I confirmed this by manually searching the data file). The cases that it does flag hit on various DX variables (DX1, DX3, DX12, etc), so it's not like the array is only searching DX15 or something like that.   %let PTB_all_list = ("O6010X","O6012X","Z3A22","Z3A23","Z3A24","Z3A25","Z3A26","Z3A27","O6013X", "O6014X","Z3A28","Z3A29","Z3A30","Z3A31","Z3A32","Z3A33","Z3A34","Z3A35", "Z3A36"); DATA want; SET have; ARRAY dx_codes DX1-DX15; do index=1 to dim(dx_codes ); IF dx_codes[index] in: (&PTB_all_list.) THEN PTB_birth_array=1; ELSE PTB_birth_array=0; end; IF (DX1 in (&PTB_all_list.) OR DX2 in (&PTB_all_list.) OR DX3 in (&PTB_all_list.) OR DX4 in (&PTB_all_list.) OR DX5 in (&PTB_all_list.) OR DX6 in (&PTB_all_list.) OR DX7 in (&PTB_all_list.) OR DX8 in (&PTB_all_list.) OR DX9 in (&PTB_all_list.) OR DX10 in (&PTB_all_list.) OR DX11 in (&PTB_all_list.) OR DX12 in (&PTB_all_list.) OR DX13 in (&PTB_all_list.) OR DX14 in (&PTB_all_list.) OR DX15 in (&PTB_all_list.)) THEN PTB_birth_line=1; ELSE PTB_birth_line=0; RUN; title1 "PTB"; PROC FREQ data=want; TABLES PTB_birth_array PTB_birth_line; RUN; And here are the frequencies:   ... View more

I'm trying to create a single field with the mean and standard deviation combined in mean(std) format. I have it mostly figured out, but the problem is that trailing zeros are being truncated. So rather than 27.00(18.70), it shows up as 27(18.7).  But numbers such as 14.21(15.04) show up properly.   In the code below, the zeros are already truncated in the "test" dataset. How do I add them back in? The final numbers will be exported to an Excel table, so I don't think I could just apply a format to them.   data test; input Mean Std; datalines; 14.2123 15.0416 27.0000 18.7000 27.3185 19.8000 ; data want; set test; StdRD = ROUND(Std,.01); MeanRD = ROUND(Mean,.01); Final_output = COMPRESS(MeanRD||"("||StdRD||")"); RUN;   ... View more

I ran into this issue recently after I renamed the ID variable in one of my files. As in the example below, I was attempting to restrict the cases in one file to the cases that were present in another file. But because the ID variable name was different, SAS ignored the Where statement. What really bothered me is that there was no error or even warning in the log. It would be very easy to overlook this problem and think that the output file has the proper cases when it doesn't.   I assume this is a known issue, but I had never heard of it. I tried searching, but I didn't find anything. Can someone point me to more information about this?   In the example below, the 1st file has 4 cases and the 2nd file has 3 cases. The PROC SQL is intended to restrict the 1st file to just those 3 cases. Instead, because the ID variable has been renamed in the 2nd file, PROC SQL returns all 4 cases with no errors in the log.     data has_ID_var; input ID test_flag; datalines; 1234 1 2345 0 3456 0 4567 1 ; run; data has_personID_var; input personID; datalines; 1234 2345 3456 ; run; PROC SQL; Create table output as Select * From has_ID_var Where ID in (select ID from has_personID_var); quit;     ... View more

In my previous post, I laid out the issue and provided example data. Unfortunately, there was a use case that I didn't think of. @ballardw , you helped me with the original solution, so I'm hoping you can help me tweak it to address this issue. But first, let me clarify that we've shortened our follow-up period from 12 months to 3 months. If the delivery date is 11/10/2018, the case needs to have continuous enrollment in Dec 2018, Jan 2019, and Feb 2019. If the mom has enrollment data for 2019 and the flags for either Jan or Feb are 0, then the case will not meet the enrollment criteria. But what if the mom was not enrolled in 2019 at all? Since there is no 0 for Jan or Feb, the flag will not be "zeroed out", and it will look like she has 3 months of continuous enrollment as long as she is enrolled in Dec 2018 only. I've updated the example data accordingly. Case 3456 should not qualify for continuous enrollment.   data test; input ID deliv_date YEAR_of_BIRTH YEAR_of_ENROLL ENRMON1 ENRMON2 ENRMON3 ENRMON4 ENRMON5 ENRMON6 ENRMON7 ENRMON8 ENRMON9 ENRMON10 ENRMON11 ENRMON12; datalines; 1234 06/01/2017 2017 2017 1 1 1 1 1 1 1 1 1 1 1 1 1234 06/01/2017 2017 2018 1 1 1 1 1 1 1 1 1 1 1 1 1234 06/01/2017 2017 2019 1 1 1 1 1 1 1 1 1 1 1 1 2345 08/15/2018 2018 2017 1 1 1 1 1 1 1 1 1 1 1 1 2345 08/15/2018 2018 2018 1 1 1 1 1 1 1 1 1 1 1 1 2345 08/15/2018 2018 2019 0 1 1 1 1 1 1 1 1 1 1 1 3456 11/10/2018 2018 2017 1 1 1 1 1 1 1 1 1 1 1 1 3456 11/10/2018 2018 2018 1 1 1 1 1 1 1 1 1 1 1 1 ; /* get dates for the enrollments and restructure*/ data useful; set test; array m (*) enrmon: ; do i=1 to dim(m); testdate= mdy(i,1,year_of_enroll); Enrollcode = m[i]; output; end; format testdate mmddyy10.; keep id -- YEAR_of_ENROLL testdate enrollcode; run; data setflags; set useful; if (intck('month',deliv_date,testdate) le 12) then Cont_flag = enrollcode; run; proc summary data=setflags nway; class id deliv_date YEAR_of_BIRTH; var Cont_flag; output out=want (drop=_: ) min= ; run; /* get dates for the enrollments and restructure*/ data useful; set test; array m (*) enrmon: ; do i=1 to dim(m); testdate= mdy(i,1,year_of_enroll); Enrollcode = m[i]; output; end; format testdate mmddyy10.; keep id -- YEAR_of_ENROLL testdate enrollcode; run; data setflags; set useful; if (intck('month',deliv_date,testdate) le 3) then Cont_flag = enrollcode; run; proc summary data=setflags nway; class id deliv_date YEAR_of_BIRTH; var Cont_flag; output out=want (drop=_: ) min= ; run;   ... View more