Check option OUTDESIGN= : proc glmselect data=cars outdesign(addinputvars fullmodel)=SplineBasis; ... View more

data want; set have; _remain = duration_days; _dd = duration_days; _amt = amount; do while (_remain > 0); duration_days = min(30,_remain); amount = _amt * (duration_days / _dd); output; _remain = _remain - duration_days; start_date = start_date + 30; end; run; ... View more

Hi @Abimal_Zippi    Sorry, I selected it as attachment, but didn't check. This time I use a code box:   /******************************************************************************/ /* RensInterval erlu 29.1.2013 */ /* */ /* Makroen anvendes til rensning af SAS-datasæt, hvor de enkelte records */ /* udtrykker tidsintervaller angivet ved en startdato og en slutdato. */ /* */ /* Makroen læser et angivet datasæt og danner et outputdatasæt, hvor */ /* flere poster med tidsintervaller i umiddelbar fortsættelse eller overlap- */ /* pende er komprimeret til eet interval. */ /* */ /* Makroen checker, om datasættet indeholder flere poster, hvor alle */ /* variabelværdier er fælles bortset fra tidsintervallet, og hvor */ /* tidsintervallerne er fortløbende eller overlappende, således at startdato */ /* på en følgende post er <= dagen efter slutdato på den foregående post. */ /* De resulterende intervalvariable får samme format som formatet på den */ /* variabel, der er angivet son datefirst. */ /* */ /* Kaldsargumenter: */ /* */ /* Positionelle: */ /* Inputdatasæt */ /* */ /* Outputdatasæt */ /* */ /* Keyword: */ /* Datefirst=datefirst Anvendes kun hvis varnavn ikke er datefirst */ /* */ /* Datelast=datelast Anvendes kun hvis varnavn ikke er datelast */ /* */ /******************************************************************************/ %macro RensInterval(thisinds,thisoutds,datefirst=DateFirst,datelast=DateLast); %local dropstmt datofmt SelectList WhereList datofmtstr; %* Check eksistens af inputdatasæt; %if %sysfunc(exist(&thisinds)) = 0 %then %do; %put ERROR: Inputdataset &thisinds findes ikke; %goto wexit; %end; %let GeneratedKey = qxzworkid; %let dropstmt = drop &GeneratedKey; %let ContentDs = qxw1; proc datasets nolist; delete qxw1 qxw2 qxw3; quit; %* Dan datasæt med variabelspecifikationer; proc contents data=&thisinds noprint short out=&ContentDs; run; %* initier datoformat; %let datofmt = %str(); %* dan makrovariable ud fra proc contents; data _null_; set &ContentDs end=eof; length SelectList WhereList $4096 fmt $60; retain SelectList WhereList; retain DatoVarChk 0; if lowcase(name) NE lowcase("&datefirst") and lowcase(name) NE lowcase("&datelast") then do; SelectList = catx(',', SelectList, name); WhereList = catx(' AND ', wherelist, 'a.'||trim(name)||'=b.'||trim(name)); end; %* fang format på datefirst; if lowcase(name) = lowcase("&datefirst") then do; if lowcase(format) = 'date' and formatl = 0 then fmt = 'DATE.'; else if lowcase(format) = 'datetime' and formatl = 0 then fmt = 'DATETIME.'; else do; fmt = trim(format)||strip(put(formatl,8.0))||'.'; if formatd > 0 then fmt = trim(fmt)||strip(put(formatd,8.0)); end; call symputx('datofmt',fmt); end; %* fang de 2 datointervalvariable i datasættet; if lowcase(name) = lowcase("&datefirst") then DatoVarChk = DatoVarChk + 1; else if lowcase(name) = lowcase("&datelast") then DatoVarChk = DatoVarChk + 1; if eof then do; call symput('SelectList',trim(SelectList)); call symput('WhereList',trim(WhereList)); call symputx('DatoVarChk',trim(DatoVarChk)); end; run; %* stop ved manglende datovariable; %if &DatoVarChk ne 2 %then %do; %put ERROR: Manglende intervalvariable - &datefirst eller &datelast ikke fundet i datasæt: &thisinds; %goto wexit; %end; %* dan datoformatstreng til at sætte format op outputdatoer; %if &datofmt = %str(0.) %then %let datofmtstr = %str(); %else %do; %let datofmtstr = format qxnewfirst qxnewlast &datofmt; %put NOTE: Format til output-intervalvariable: &datofmt; %end; %put NOTE: SelectList = &SelectList; %put NOTE: WhereList = &WhereList; %* dan id-nummereret distinct liste over variable (undtagen datovariable); proc sql; create table qxw2 as select monotonic() as &GeneratedKey, &SelectList from (select distinct &SelectList from &thisinds as b) as a order by &GeneratedKey; quit; %* flet id-numrene ind på hele datasættet og sorter; proc sql; create table qxw3 as select a.*, b.&GeneratedKey from &thisinds as a, qxw2 as b where &WhereList order by &GeneratedKey, &datefirst, &datelast; quit; %* Dan resulterende intervaller; data &thisoutds(rename=(qxnewfirst=&datefirst qxnewlast=&datelast)); set qxw3; by &GeneratedKey; retain qxnewfirst qxnewlast; &datofmtstr; drop &datefirst &datelast; &dropstmt; if first.&GeneratedKey then do; qxnewfirst = &datefirst; qxnewlast = &datelast; end; else do; if &datefirst > qxnewlast + 1 then do; output; qxnewfirst = &datefirst; qxnewlast = &datelast; end; else if &datelast > qxnewlast then qxnewlast = &datelast; end; if last.&GeneratedKey then output; run; %wexit: proc datasets nolist; delete qxw1 qxw2 qxw3; quit; %mend; ... View more

You've now clarified your object.  For any pair of consecutive observations within an ID, you want to assign flag=1 for both of those observations if they have at least 2 variables with consecutive values of 1.   This is a single step solution that, for each ID, reads the series of observations twice.  In the first pass, each record F=1,2,3, ... is compared to the prior record and a temporary variable consec_ones{F} counts the number of vars with a value of 1 in the current and prior record.   The second pass, rereads the series, and for each record (S=1,2,3) and if the current record has consec_ones>1 or the next record has consec_ones > 1 then set the flag:   data have; input id $ year month var1-var7 ; datalines; 1 2012 7 0 0 0 0 1 1 1 1 2012 8 0 0 0 0 0 1 1 1 2012 9 1 0 0 0 0 0 0 1 2012 10 0 0 1 0 0 0 0 1 2012 11 0 1 0 1 0 1 0 1 2012 12 0 0 0 0 0 0 1 1 2013 1 0 0 1 1 1 0 0 2 2012 6 0 0 1 1 1 0 0 2 2012 7 0 0 0 0 0 0 0 2 2012 8 0 0 0 0 0 0 0 2 2012 9 0 0 0 0 0 0 0 2 2012 10 0 0 0 0 0 0 0 2 2012 11 0 0 0 0 0 0 0 2 2012 12 0 0 0 1 0 1 0 2 2013 1 0 0 1 1 1 0 0 2 2013 3 1 1 0 0 0 0 0 3 2012 7 0 0 0 0 1 1 1 3 2012 8 0 0 0 0 0 0 0 3 2012 9 0 0 0 0 0 0 0 3 2012 10 0 0 1 0 0 0 0 4 2012 11 0 1 0 0 0 1 0 4 2012 12 0 0 0 0 0 0 0 4 2013 1 1 0 0 0 0 1 0 4 2013 4 0 0 1 1 0 0 0 run; data want (drop=v f s); set have (in=firstpass) have (in=secondpass); by id; array consec_ones{30} _temporary_; array var{*} var: ; if first.id then call missing(F,S,of consec_ones{*}); if firstpass then do; F+1; consec_ones{F}=0; do v=1 to dim(var); if dif(var{v})=0 and var{v}=1 then consec_ones{F}+1; end; end; if first.id then consec_ones{1}=0; if secondpass; S+1; flag= max(consec_ones{S},consec_ones{S+1})>1; run;     Edit:  I added the if first.id then consec_ones{1}=0; statement to eliminate artificial creation of consec_one{1} from comparing the beginning of one ID to its predecessor at the end of the previous ID.       Notes: Set the size of the array consec_ones to at least 1 greater than the largest number of observations expected for any ID.  I used 30, meaning I expected no more that 29 records for any ID. F=1,2,3,... for record sequence within the first pass through an ID.   S=1,2,3 for the same during the second pass. The DIF(x) function is equivalent to X-lag(X).  So if DIF(x)=0 and the current value of X=1, then you've revealed consecutive 1's.   The major "trick" here is to interleave dataset HAVE with itself  - the pair of statements SET HAVE (in=firstpass) HAVE (in=secondpass); BY ID; .  ... View more