I have a dataset with id, release_dare, and a a variable called datenum Id $4 release_date mmddyy. Datenum $4 There are several rows per id since each can have many release dates. I want to create a one row per person with the following: Id, month1-month24 that each contains the datenum variable for the corresponding month of the release from release_date. If there sre 2 or more on the same month, concatenate the datenum. So if i have this as input: Id. Release_date. Datenum 1 03/02/11 1245 2 01/01/11 3423 2 01/10/11 5432 At the end, i want this Id. month1. month2. month3. 1. 1245 2. 34235432 ... View more

I have data on about 30000 records, each record can have up to 17 release dates (release1-release17) and up to 17 codes decribing the release type (street1-street17- for whether or not the immate was released to the streets).     So now I want to create 60 new variables: Month1-Month60 covering the 60 months of the data of Jan 2011-Dec 2015, where Month1 is January 2011 and Month60 is December 2015. If the variable street1-street17 is Y that means if was a "true" release and this new variable (MonthY) will be the difference in days for that month and a predefined date until the next release. If multiple release dates occur on the same month, then I need to concatenate these differences. Only 5 release dates per month will be concatenated. Dates are in this format mm/dd/yyy and the predefined date is 12/28/2035.   I have 2 problems with my code- the second release is being put on the wrong month (for example it should be month33 but it is showing on Month 34). And the other problem is that it is not concatenating multiple releases on the same month.   any help will be appreciated, thanks, malena     here is my code:     *Create a record for each month based on  release date; data have_monthly; length cat $24.; length cat2 $24.; set have_flipped; N_interval = intck('month', date_type, date_release); /*If NOT MISSING(date_admission) then do;*/ if N_interval <> . then do; Month=intnx('month', date_release, i-1, 'b'); Status=1; cat=catx(',',cat,date_type); cat2=compress(cat, ','); if i=(N_interval+1) and release_type='Y' then status=0; output; end; format month yymon7.; keep studyID month status cat2; run; /*This section of code creates the empty data set required for the final data structure one record per month, per CHIA_random_ID */ *Create months; data empty_months; start = '01Dec2010'd; do i=1 to 60; Month=intnx('month', start, i, 'b'); Monthly_Number = catt("Month", put(i, z2.)); output; end; format month yymon7.; keep month Monthly_Number; run; *Get list of IDs; proc sql; create table ids as select distinct id from have; quit; *Create monthly by ID table with no status; proc sql; create table empty_status as select a.id, b.* from empty_months as b cross join ids as a order by 1, 2; quit; *Add in status from previous calculation; proc sql; create table status as select a.*, b.cat2 from empty_status as a left join have_monthly as b on a.id=b.id and a.month=b.month order by 1,2; quit; *fill down values so values are retained across time; data status_filled; length status 8.; set status; by id; retain final_status; if first.id then final_status=status; else if not missing(status) then final_status=status; status=final_status; if missing (status) then status=0; drop final_status; run; proc sort data=status_filled out= status_filled2 nodupkey equals; by id monthly_number; run; *Transpose back to final wide data structure; proc transpose data=status_Filled2 out=want(drop= _name_); by id; var cat2; id monthly_number; idlabel month; run; proc sort data=incarcerated out= incarcerated2 nodupkey equals; by id; run;     data data_final; set want; DOC1=Month01; DOC2=Month02; DOC3=Month03; DOC4=Month04; DOC5=Month05; DOC6=Month06; DOC7=Month07; DOC8=Month08; DOC9=Month09; DOC10=Month10; DOC11=Month11; DOC12=Month12; DOC13=Month13; DOC14=Month14; DOC15=Month15; DOC16=Month16; DOC17=Month17; DOC18=Month18; DOC19=Month19; DOC20=Month20; DOC21=Month21; DOC22=Month22; DOC23=Month23; DOC24=Month24; DOC25=Month25; DOC26=Month26; DOC27=Month27; DOC28=Month28; DOC29=Month29; DOC30=Month30; DOC31=Month31; DOC32=Month32; DOC33=Month33; DOC34=Month34; DOC35=Month35; DOC36=Month36; DOC37=Month37; DOC38=Month38; DOC39=Month39; DOC40=Month40; DOC41=Month41; DOC42=Month42; DOC43=Month43; DOC44=Month44; DOC45=Month45; DOC46=Month46; DOC47=Month47; DOC48=Month48; DOC49=Month49; DOC50=Month50; DOC51=Month51; DOC52=Month52; DOC53=Month53; DOC54=Month54; DOC55=Month55; DOC56=Month56; DOC57=Month57; DOC58=Month58; DOC59=Month59; DOC60=Month60; run; data Data_final; merge incarcerated2  DOC_final; by studyid; keep id gender age race DOC1-DOC60 times; run;   ... View more

I will like to write a program that creates a file with 19 records with several columns. The issue is that in some cases a column willb e based on a value for a subsequent record, not yet created.   For example: here for the 3rd record (ageg=5-9), the value of dx is based on the lx value for the 4th record - the lx value for the 3rd record. Does SAS allow you to some these kind of calculations?    Ageg x n a x pop death M x q x p x l x d x <1 0 1 0.1 73200 321 0.004385 0.0043680 0.995632 100000 437 1-4 1 4 0.5 292855 57 0.000195 0.0007782 0.999222 99563 77 5-9 5 5 0.5 376322 32 0.000085 0.0004251 0.999575 99486 42 10-14 10 5 0.5 398163 40 0.000100 0.0005022 0.999498 99443 50 15-19 15 5 0.5 457196 131 0.000287 0.0014316 0.998568 99393 142 20-24 20 5 0.5 491392 310 0.000631 0.0031493 0.996851 99251 313 25-29 25 5 0.5 482730 497 0.001030 0.0051346 0.994865 98939 508 30-34 30 5 0.5 447672 508 0.001135 0.0056577 0.994342 98431 557 35-39 35 5 0.5 404949 521 0.001287 0.0064123 0.993588 97874 628 40-44 40 5 0.5 432783 709 0.001638 0.0081578 0.991842 97246 793 45-49 45 5 0.5 475213 1233 0.002595 0.0128895 0.987110 96453 1243 50-54 50 5 0.5 511398 1975 0.003862 0.0191252 0.980875 95210 1821 55-59 55 5 0.5 479748 2787 0.005809 0.0286307 0.971369 93389 2674 60-64 60 5 0.5 405550 3219 0.007937 0.0389146 0.961085 90715 3530 65-69 65 5 0.5 330937 4086 0.012347 0.0598853 0.940115 87185 5221 70-74 70 5 0.5 231859 4592 0.019805 0.0943540 0.905646 81964 7734 75-79 75 5 0.5 165604 5300 0.032004 0.1481655 0.851834 74230 10998 80-85 80 5 0.5 130643 7484 0.057286 0.2505474 0.749453 63232 15843 85+ 85 15 0.5 157194 21355 0.135851 1.0000000 0.000000 47389 47389 ... View more