I have two datasets. One old and one new...if there are new increase in numbers  for selected columns (i.e. Enrolled and Events), I would like to display "(+<Number>). old export: Dataset_a Group Screened Enrolled Events ABC 40 21 3 DEF 24 21 4 GHI 55 41 5 JKL 24 2 6 MNO 42 23 8 new export: Dataset_b Group Screened Enrolled Events ABC 89 33 13 DEF 56 43 4 GHI 88 45 22 JKL 67 23 34 MNO 68 23 23 Want: Group Screened Enrolled Events ABC 89 33 (+12) 13 (+10) DEF 56 43 (+22) 4 GHI 88 45 (+4) 22 (+17) JKL 67 23 (+21) 34 (+28) MNO 68 23 23 (+15)   I have merged two datasets and used subtraction with concentrate character so was wondering if this is quick solution   ... View more

I have the following dataset. What I would like to do is find NEW enteries based on ID and instance # into the new export but retain the new information old: ID group instance term term_2 1 1 1 AAAA one 1 2 indirect 2 CCCC seven 1 2 indirect 3 DDDD seven 2 1 1 ASFD two 2 2 indirect 2 DDA three 3 2 direct 1 ERQE one 4 1 1 ABCD seven 4 1 2 ABCD five for exmaple this is the new dataset that I would like to create. If ID and instance doesn't exist "old" dataset, then I would like to flagT the entries into the another dataset. new export: ID group instance term term_2 1 1 1 AAAA one 1 2 indirect 2 CCCC seven 1 2 indirect 3 DDDD seven 1 2  indirect 4 DFDD two 2 1 1 ASFD two 2 2 indirect 2 DDA three 3 2 direct 1 ERQE one 4 1 1 ABCD seven 4 1 2 ABCD five 4 2 direct 3 CCCC one 5 1 1 ERQE seven   Want   ID group instance term term_2 flagT 1 1 1 AAAA one 0 1 2 indirect 2 CCCC seven 0 1 2 indirect 3 DDDD seven 0 1 2  indirect 4 DFDD two 1 2 1 1 ASFD two 0 2 2 indirect 2 DDA three 0 3 2 direct 1 ERQE one 0 4 1 1 ABCD seven 0 4 1 2 ABCD five 0 4 2 direct 3 CCCC one 1 5 1 1 ERQE seven 1   I did the following: proc sql; create table old_flag as  select id, max(instance) as instance from old group by id; quit;   proc sql; create table new_flag as select id, max(instance) as instance from new group by id; quit;   not sure what to do next ... View more

Hello I have 3 datasets and as you can see, Ids from one table compared to another is not always the case.    I need to merge all 3 datasets by id and if variable(s) don't exist then they need to be blank:   Dataset1: ID age gender 12 15 F 23 18 F 33 16 M 42 14 F   Dataset2: ID SchoolYear City State 12 Freshmen Brookline MA 42 Freshmen Brookline MA 46 Senior Framingham MA 48 Senior Quincy MA   Dataset3 ID MathGrade EnglishGrade 23 75 65 46 81 99 48 95 65   Want ID age gender SchoolYear City State MathGrade EnglishGrade 12 15 F Freshmen Brookline MA     23 17 F       75 65 33 16 M           42 14 F Freshmen Brookline MA     46     Senior Framingham MA 81 99 48     Senior Quincy MA 95 65   What i have done so far is the following: proc sql;  create table uniqueID as  select distinct ID from dataset1 union select distinct ID from dataset2 union select distinct ID from dataset3 ; quit;   then proc sql; select a.*, b.*, c.* from uniqueID a  left join dataset1 b on a.ID = b.ID left join dataset2 b on a.ID = b.ID left join dataset3 c on a.ID = c.ID; quit; not geting what i look for.  ... View more

Hello. I have a bunch of datasets in a library called: 'TrialData'. I have the format library stored in the 'TrialData' as well.  However, when I try to apply the format in the same 'TrialData' library, it doesn't work. I have to save them under 'Work' library. Is there any way it can be fixed? Thanks!   PROC FORMAT LIBRARY=TrialData; VALUE Assessment 1='Yes' 0='No' 99 ='Missing or not reported'; VALUE CommitmentStatus 1='Yes' 0='No' 99 = 'Missing or not reported; RUN;   Applying format to one of the datasets = Dataset1 DATA TrialData.Dataset1; SET TrialData.Dataset1; FORMAT Assessment Assessment. CommitmentStatus CommitmentStatus .; RUN;   It doesn't work. It only works if I load the format in the Work library   Thanks so much! ... View more

Hello . I need help with combining two datasets with all time points for each ID even though they are not in dataset #2:   dataset #1: 'event' table event Month 1 Month 2 Month 3 dataset#2 'id_all' ID event  value value2 5656 Month 1 13 324 6565 Month 2 31 465 4663 Month 1 14 697 4242       1234 Month 3 14 656 5421 Month 1 12 647   want: ID event  value value2 5656 Month 1 13 324 5656 Month 2     5656 Month 3     6565 Month 1     6565 Month 2 31 465 6565 Month 3     4663 Month 1 14 697 4663 Month 2     4663 Month 3     4242       4242 Month 1     4242 Month 2     4242 Month 3     5421 Month 1 12 647 5421 Month 2     5421 Month 3     1234 Month 1     1234 Month 2     1234 Month 3 14 656   here's my attempt so far: proc sql; select  a.*, b.event from id_all a right join event b on a.event = b.event; quit;   doesn't work:( ... View more

I would like to take MEAN and Standard Deviation by Arms for Var1 through Var6   Here is the dataset ID Var1 Var2 Var3 Var4 Var5 Var6 Arm 1 5 6 1 9   9 A 2 6 1   11 56 12 B 3 9   12 22     A 4   3 6 6 10   A 5 6 6 3     5 A 6 15 4   7 6 9 A 7   1 4       B   PROC MEANS DATA = test NOPRINT COMPLETETYPES; FORMAT Arm Arm.; CLASS Arm/PRELOADFMT; VAR Var1 Var2 Var3 Var4 Var5 Var6; OUTPUT OUT = test2(keep = Arm N MEAN STD) N = N MEAN = MEAN STD = STD; RUN;   It doesn't pick up all Vars. Just the first one ... View more

I have the following dataset. if the value is blank I need the count variable to be  to be blank. Otherwise count all the comma + 1 Have ID VALUE ABC 2 CDE   EEA 2,3 AWE 4,5,6 DDE 5 QWE   WQE 5,3,2   Have: iD VALUE count ABC 2 1 CDE     EEA 2,3 2 AWE 4,5,6 3 DDE 5 1 QWE     WQE 5,3,2 3 Here's what I have if missing(VALUE) then count = .; else count = countc(VALUE, ',')  + 1; ... View more

I have the following data ID Date group 1 1/2/2021 1 1 1/3/2021 2 2 1/2/2021 2 3 1/5/2021 2 4 1/4/2021 1 4 1/5/2021 2   What I would like to do is that for each ID, I need to take the max group and add additional groups if ID only has one instance then keep that new_group as is if the ID has more than one instance then new_group = '2 Indirect' like this   then only keep the new group ID Date group new_group 1 1/2/2021 1   1 1/3/2021 2 2 Indirect 2 1/2/2021 2 2 3 1/5/2021 2 2 4 1/4/2021 1   4 1/5/2021 2 2 Indirect   i did the following proc sql; select *, max(group) as group from data; group by id; quit;  ... View more

I have the following table ID group race gender ethnicity 1 A white female hispanic 2 A white female not hispanic 3 A black male unknown 6 A unknown female hispanic 4 B asian male not hispanic 5 B black female not hispanic   I would like to do proc tabulate by 'group' that has total n per column and percentage per column   something like this:     A B N   4 2 race white 2 (50%)     black 1 (25%) 1 (50%)   asian   1 (50%)   unknown 1 (25%)   gender female 3 (75%) 1 (50%)   male 1 (25%) 1 (50%) ethnicity Hispanic 2 (50%)     not Hispanic 1 (25%) 2 (100%)   unknown 1 (25%)   proc tabulate data=test format=8.2 out =test2; class group; table race all, (race all)*(n*f=8. reppctn) / rts=10; ethnic all, (ethnic all)*(n*f=8. reppctn) / rts=10; gender all, (gender all)*(n*f=8. reppctn) / rts=10; run;       ... View more

I have the following dataset which is sorted by SEQ because that is the order i would like to keep for the CSV output   proc sort data = test; by seq;run; Group1 Group 2 Value1 Value2 Value3 SEQ Total   12 4 16 1 ADE SDF 5 1 2 2 EFE ERW 1 1 8 3 DSF DGR 1 1 1 4 DFD WER 5 1 5 5   However when I proc report export to the CSV, it shows 'total' in the bottom 'seq' sorting is gone.    ods csv file = 'filepath\test.csv.'; proc report data = test nowindows headline headskip; column group1 group2 value1 value2 value3; define group1/display = "Group 1" width =14; define group2/display = "Group 2" width =14; define value1 /display = "Value 1" width =14; define value2/display = "Value 2" width =14; define value3/display = "Value 3" width =14; run; ... View more