Hi, here's another idea. I made the maximum number of diseses five (D1-D5) and the maimum lenght of any disease twenty. You could make those values anythimg you like.   data x; infile datalines dsd dlm='^' missover; input @; substr(_infile_,find(_infile_,' '),1) = '^'; input id :$5. (d1-d5) (:$20.); count = countc(_infile_,'^'); datalines; X1 Fever^Acidity^Vomiting X2 Chills^Nausea X3 Bad Vibes^Bummed^Just Plain Tired X1234 Upset Stomach ;   DATA SET: x id d1 d2 d3 d4 d5 count X1 Fever Acidity Vomiting 3 X2 Chills Nausea 2 X3 Bad Vibes Bummed Just Plain Tired 3 X1234 Upset Stomach 1 ... View more

Hi, still needs a couple fixes, only worked since the  max date is in the first group of addresses.   Will fix later and send to you. My email is msz03@albany.edu ... View more

For maximum date in the data set for all PROPERTIES ...   data y; retain max_date; array _a(0:1000); do j=1 by 1 until (last.address);   set xx;   by address;   if first.address then first = date;   _a(date - first) = amount;   max_date = max(max_date, date); end; do j=0 to max_date-first;   value = sum(value,_a(j));   date = first+j;   if day(date+1) eq 1 then output; end; keep address date value; run;   Send me an email ... msz03albany.edu ... and I'll do the walk through for you. ... View more

Hi, SPARSE does not add missing datesd to the data set ... yes/no? PROC FREQ can only count values that are present in the data. SPARSE only insures that table cells with ZERO values end up in a data set  ... View more

Hi, this uses an array but not in the way (I think) that you proposed. There probably is a more efficient INTNX-related metod, but  this fills in the missing months ...   * aggregate amounts by address and date; proc summary data=x nway; class address date; var amount; output out=xx (drop=_:) sum=; run;   * fill in the missing months; data y; array _a(0:1000); do j=1 by 1 until (last.address);   set xx;   by address;   if first.address then first = date;   _a(date - first) = amount; end; do j=0 to (date - first);   value = sum(value,_a(j));   date = first+j;   if day(date+1) eq 1 then output; end; keep address date value; run;   Portion of output ... DATA SET: y address date value Property1 31/01/2014 -73649.60 Property1 28/02/2014 -73649.60 Property1 31/03/2014 -73649.60 Property1 30/04/2014 -73649.60 Property1 31/05/2014 -73649.60 Property1 30/06/2014 -73649.60 Property1 31/07/2014 -74309.60 Property1 31/08/2014 -74309.60 Property1 30/09/2014 -74309.60 Property1 31/10/2014 -76047.60 Property1 30/11/2014 -76047.60 Property1 31/12/2014 -76047.60 Property1 31/01/2015 -76047.60 Property1 28/02/2015 -76047.60 Property1 31/03/2015 -76047.60 Property1 30/04/2015 -76047.60 Property1 31/05/2015 -76047.60 Property1 30/06/2015 -76892.92 Property1 31/07/2015 -76892.92 Property1 31/08/2015 -76892.92 Property1 30/09/2015 -76892.92 Property1 31/10/2015 -76892.92 Property1 30/11/2015 -76892.92 Property1 31/12/2015 -76047.60         ... View more

Hi, if it's all the data sets in that library, here's an example ... libname z 'z:\';   data z.one; retain x 10; run;   data z.two; retain x 10; run;   * concatenate the data set names into a macro variable; proc sql; select catt('z.',memname) into :dsets separated by ' ' from dictionary.tables where libname = 'Z'; quit;   data new; set &dsets; run;   If your data sets had a common numeric suffix, it's easier ... data z.d1; retain x 10; run;   data z.d2; retain x 10; run; data new; set z.d1-z.d2; run; ... View more

Hi, another idea if there are a lot of different values of VARIABLES 1 and 2 ...   proc sql; create table fmt as select "$v1_" as fmtname, var1 as start, "ok" as label from have group by var1 having count(*) ge 3 union select "$v2_" as fmtname, var2 as start, "ok" as label from have group by var2 having count(*) ge 3; quit;   * uncomment SELECT if you want to see the formats; proc format cntlin=fmt; *select $v1_ $v2_; run;   data want; set have; if put(var1,$v1_.) eq "ok" and put(var2,$v2_.) eq "ok"; run;   ... View more

Hi, another idea that also use PROC SQL first (I see you have variable1 & 2 as caharcter data so the values are quoted in SQL)...   proc sql; select quote(var1) into :v1 separated by ' ' from have group by var1 having count(*) ge 3; select quote(var2) into :v2 separated by ' ' from have group by var2 having count(*) ge 3; quit;   data want; set have; if var1 in (&v1) and var2 in(&v2); run;   DATA SET: want id var1 var2 9914108 100671 91996 9914115 100690 91999 9914119 100690 91996 9914124 9512799 91996 9914130 100671 91996 9914136 9512799 91996 9914158 100690 91999 9914163 100671 91999 9914164 9512799 91999 ... View more

Hi, another (belated) idea though you end up with sorted names ...   data x; input (name1-name4) (:$2.); datalines; AA BB CC DD AA AA BB EE FF FF CC CC AA AA AA AA BB BB BB ZZ AA CC CC AA JJ KK KK KK ZZ YY ZZ YY QQ QQ EE FF ;   data y (drop=names); length names $30; set x; call sortc(of name1-name4); names = catx(' ',of name: ); howmany = (count(tranwrd(names,name1,''),name2) ne 0) +           (count(tranwrd(names,name2,''),name3) ne 0) +           (count(tranwrd(names,name3,''),name4) ne 0) + 1; run;   Or ...   data y (drop=j); set x; array name(4); call sortc(of name1-name4); howmany = 1; do j=1 to 3;    howmany + ^^find(tranwrd(catx(' ',of name: ),name(j),''),name(j+1)); end; run;   (^^find ... changes result from location to 1 if found, 0 if not ... thanks to Howard Schreier)    DATA SET: y name1 name2 name3 name4 howmany AA BB CC DD 4 AA AA BB EE 3 CC CC FF FF 2 AA AA AA AA 1 BB BB BB ZZ 2 AA AA CC CC 2 JJ KK KK KK 2 YY YY ZZ ZZ 2 EE FF QQ QQ 3   ... View more