Yes, is this possible to do? I'm quite unfamiliar with proc report. ... View more

Or to use an even simpler example:   proc report data=Merged split='~' nowd ; column status sex bp_status Smoking_Status,(MRW); define Smoking_Status / Across; define status / Group; define sex / Group; define bp_status / Group; define MRW / sum ''; run; The same holds true here too with the empty box in the results table. ... View more

Thanks Cynthia,   In terms of resetting Options to default after I have run my proc report, is there a standard way of doing this or would you just write Options Missing=.; to reset? ... View more

Ahh yes sorry, this is dd/mm/yyyy. order=internal has sorted, thanks 🙂 ... View more

Thanks, two questions re. this:   1) if i use options missing=0, is there a way of changing options back to default (where missing=.) other than just running options missing=.;  ?   2) How exactly would I go about using a default format in proc report? I have tried to run proc format; value Probability .=0; run; proc report data=Merged out=a; column SC1 SC2 SC3 SC4 ap_month,(Probability); define ap_month / Across; define SC1 / Group; define SC2 / Group; define SC3 / Group; define SC4 / Group; define Probability / sum format=probability.; run; but this just makes every value in my output table = 0! ... View more

@mkeintz    The missing option does not seem to help unfortunately.   Here is some example code: data person; input GroupA GroupB GroupC ap_month $ one; datalines; 1 1 1 Jan2010 1 1 1 2 Jan2010 1 1 2 1 Jan2010 1 2 1 1 Jan2010 1 2 2 1 Jan2010 1 1 1 1 Jan2011 1 1 1 2 Jan2011 1 1 2 1 Jan2011 1 2 1 1 Jan2011 1 ; /*2 2 1 Jan2011 1*/ run; proc tabulate data = Person out = want; class GroupA GroupB GroupC ap_month; var one; table GroupA=''*GroupB=''*GroupC=''*sum=''*(one*f=comma14.0),ap_month /nocellmerge ; run; proc tabulate data = Person out = want2; class GroupA GroupB GroupC ap_month; var one; table GroupA=''*GroupB=''*GroupC=''*sum=''*(one*f=comma14.0),ap_month /nocellmerge printmiss; run; Here you can see an example of the situation I have going on. You can see the commented out line is basically the missing data - which is then missing in the first proc tabulate table. By using printmiss, it expands the table (which i dont want) and, whilst the wanted output observation is now included in the output data, all the other observations relating to this bigger table are now also included - which i dont want! ... View more

Hi, here is some example code: data person; input GroupA GroupB GroupC ap_month $ one; datalines; 1 1 1 Jan2010 1 1 1 2 Jan2010 1 1 2 1 Jan2010 1 2 1 1 Jan2010 1 2 2 1 Jan2010 1 1 1 1 Jan2011 1 1 1 2 Jan2011 1 1 2 1 Jan2011 1 2 1 1 Jan2011 1 ; /*2 2 1 Jan2011 1*/ run; proc tabulate data = Person out = want; class GroupA GroupB GroupC ap_month; var one; table GroupA=''*GroupB=''*GroupC=''*sum=''*(one*f=comma14.0),ap_month /nocellmerge ; run; proc tabulate data = Person out = want2; class GroupA GroupB GroupC ap_month; var one; table GroupA=''*GroupB=''*GroupC=''*sum=''*(one*f=comma14.0),ap_month /nocellmerge printmiss; run; Here you can see an example of the situation I have going on. You can see the commented out line is basically the missing data - which is then missing in the first proc tabulate table. By using printmiss, it expands the table (which i dont want) and, whilst the wanted output observation is now included in the output data, all the other observations relating to this bigger table are now also included - which i dont want! ... View more

This is perfect, thanks! One other thing, in the very first picture I sent (as part of my original question) you can see that the dates are not in order - the last 2 columns are 01/11/2020 and 01/12/2020. Is there any way of making these the first 2 columns instead? I'm not sure why proc report has ordered this way. ... View more

My data is set up like this:     so i think it has already been grouped and doesnt need the proc sql step?   However, if i now run:   title 'Check_CIS DPM'; proc report data=Merged2 split='~'; column SC1 SC2 SC3 SC4 ap_month,(Probability); define ap_month / Across; define SC1 / Order; define SC2 / Order; define SC3 / Order; define SC4 / Order; define Probability / sum; run; I get:     Is this what you mean I should do? ... View more

Hi sorry im not entirely sure what you mean by this. Are you saying that I need to run:   title 'Check_CIS DPM'; proc report data=Merged2 split='~'; column SC1 SC2 SC3 SC4 ap_month,(Probability); define ap_month / Across; define SC1 / Order; define SC2 / Order; define SC3 / Order; define SC4 / Order; define Probability / sum; run; and if this doesnt work then beforehand i need to run:   PROC SQL; create table Merged2 as select SC1 ,SC2 ,SC3, SC4 ,ap_month,sum(Probability) as Probability from Merged group by SC1 ,SC2 ,SC3, SC4 ,ap_month; quit; and then run again? If so this doesnt seem to work! ... View more