I set out to create a summary demographics table for a dataset with 6 variables: groups, sex, age, race, ethnicity, and status as below.        Total Group A Group B Group C Group D Group E     N % N % N % N % N % N % Sex                             Female                           Male                         Age                             <20                           20-30                           ≥30                         Etc...                           Total                             Using the proc tabulate code below I am pretty close: proc tabulate data=c; class groups sex age race ethnicity status; classlev sex age race ethnicity status /s=[JUST=R]; var newcount; table sex="Sex" age="Age" race="Race" ethnicity="Ethnicity" status="Status" ALL="TOTAL", ALL="TOTAL"*(newcount="N"*f=8.0 colpctn="%"*f=8.1) groups*(newcount="N"*f=8,0 colpctn="%"*f=8.1); run;   But, I am having a few issues: 1) There is a "Sum" cell below my "N". How can I remove?  N % N % N % N % N % N % Sum Sum Sum Sum Sum Sum   2) Though my numbers are right, my column percents are not accurate. I am not sure where they are coming from, but I imagine it has to do with my proc summary or another step below.    As a test, I ran a proc freq of sex*group with weight by newcount, which gets me what should be the correct column percentages (although my frequencies are no longer the table N). I tried weighting my proc tabulate by newcount but that only significantly increases the N without changing the %.    To retrace my steps:  1)  I ran a proc summary for the variables of interest as below:    proc summary data=a; class groups sex age race ethnicity status; var count; output out=b n= /autoname; run; 2)  I ran a data step to get the monthly average since the original data is a year long and I want to present the average monthly demographics.  data c; set b; newcount=count_N/12; format newcount 8.0 ; drop count_n _freq_ ; run; 3) I ran the proc tabulate as above.      I know this probably has a simple solution, but I've been struggling with it for several hours and just can't see what I'm missing. Thanks in advance!   ... View more

I have a data set with a numeric month variable in monyy. format. I'm having trouble doing two things:    1) I want to create indicator variables that are 0 until a certain month, after which they become 1. The below code resulted in the included errors for any months I used. I tried using 'FEB2010'd as well with same result for all months attempted.  If month >= 'FEB10'd then x1=1; Else x1=0; /*Resulting Error*/ /*ERROR: Invalid date/time/datetime constant 'FEB10'd. /*ERROR 77-185: Invalid number conversion on 'FEB10'd. In my sample below I want x1 to become 1 from Feb2010 onward, and x2 to become 1 from Apr2010 onward.    2) I also want to create variables that count months starting at certain months. In the below sample I have a variable I want to start counting upward each month by 1 beginning in Mar2010. The code I tried using in the example below was count1=intnx('month', 'MAR2010'd, 1), but I receive the same error as detailed above.    I feel like this is a simple solution, but I am having trouble figuring it out. I always struggle with dates. Thank you for your help!    data have; input month monyy. exp var_a; format month monyy.; datalines; JAN10 1 100 JAN10 0 245 FEB10 1 75 FEB10 0 115 MAR10 1 100 MAR10 0 80 APR10 1 50 APR10 0 75 MAY10 1 100 MAY10 0 115 ; run; data have; input month monyy. exp var_a x1 /*1 from FEB2010*/ x2 /*1 from Apr2010*/ count1 /*starts counting from Mar2010*/; format month monyy.; datalines; JAN10 1 100 0 0 0 JAN10 0 245 0 0 0 FEB10 1 75 1 0 0 FEB10 0 115 1 0 0 MAR10 1 100 1 0 1 MAR10 0 80 1 0 1 APR10 1 50 1 1 2 APR10 0 75 1 1 2 MAY10 1 100 1 1 3 MAY10 0 115 1 1 3 ; run;  ... View more

Hi. I have a dataset in a wide format where IDs have multiple observations. This was based on where an individual experienced an outcome, underwent demographic changes, group assignment, etc. where a new row was created. I have stripped the superfluous variables and created an abbreviated sample data below. This abbreviated wide data set has study_id, an exposure variable, a group variable, and an indicator of presence for all months in the sample (1=present and contributing person-time, 0=absent), as below: data have; input study_id $ exp $ group $ mon1 mon2 mon3 mon4 mon5 mon6; datalines; S001 exposed a 1 1 0 0 0 0 S001 exposed a 0 0 1 1 0 0 S001 exposed a 0 0 0 0 1 0 S002 exposed b 1 0 0 0 0 0 S002 exposed b 0 1 1 0 0 0 S002 unexposed c 0 0 0 1 1 1 S003 unexposed c 1 1 1 1 1 1 S004 unexposed c 1 1 1 0 0 0 S004 unexposed d 0 0 0 1 1 1 ; RUN; To prepare for a subsequent analysis, I would like to sum person time by month, exposed status, and group, as follows. I would also like to add a formatted monyy. variable so I can see where in the year each month occurs, as this dataset spans several years.  data want; input month monyy. month_num exp $ group $ ptime; format month monyy.; datalines; JAN10 1 exp a 1 JAN10 1 exp b 1 JAN10 1 unexp c 2 JAN10 1 unexp d 0 FEB10 2 exp a 1 FEB10 2 exp b 1 FEB10 2 unexp c 2 FEB10 2 unexp d 0 MAR10 3 exp a 1 MAR10 3 exp b 1 MAR10 3 unexp c 2 MAR10 3 unexp d 0 APR10 4 exp a 1 APR10 4 exp b 0 APR10 4 unexp c 2 APR10 4 unexp d 1 MAY10 5 exp a 1 MAY10 5 exp b 0 MAY10 5 unexp c 2 MAY10 5 unexp d 1 JUN10 6 exp a 0 JUN10 6 exp b 0 JUN10 6 unexp c 2 JUN10 6 unexp d 1 ; run; I understand this may not be the most optimally organized dataset as it stands, but this is what I am working with at present. I may ask an additional question to see if some of this wide dataset formatting could have been bypassed using other start/stop date variables in the dataset, but I will do that in another thread when I have some time.   In its current form, does anyone have any ideas on the most optimal way to get from the multiple wide month variables in "have" to summed person time by month/exp/group as depicted in "want"?    ... View more