Thanks a lot ... View more

Hi Patrick Thanks for your prompt reply. Is there anyway you can separate the pattern and prxsubstr code into two data steps, I want to use the same pattern for multiple data. Thanks a lot   ... View more

If I understand correctly you need for each client same date range and if there is a visit then the flag will be 1 else 0.    data dates; %let startDate='01Jan2022'd; %let endDate='31Dec2022'd; do date=&startdate to &enddate; output; end; format date mmddyy10.; run; Proc sql; Create table want as Select a.date , b.Hospital_name , a.Client_Id , case when b.date then 1 else 0 end as flag from (Select *, Client_Id from dates cross join (Select distinct Client_Id from have) ) as a left join have as b on a.date=b.date and a.Client_Id=b.Client_Id order by a.Client_Id, a.date ;Quit; Sample Output: date Hospital_name Client_Id flag 1/01/2022 A Tom 1 1/02/2022 A Tom 1 1/03/2022   Tom 0 1/04/2022   Tom 0 1/05/2022   Tom 0 1/06/2022   Tom 0 1/07/2022   Tom 0 1/08/2022   Tom 0 1/09/2022   Tom 0 1/10/2022   Tom 0 1/11/2022   Tom 0 1/12/2022   Tom 0 01/13/2022   Tom 0     ... View more

Try this: Proc sql; Create table want as Select *, max(myDate) as maxDate format date9. from have ;Quit; Output: caseid myDate maxDate 34 27-Jul-10 27-Jul-10 35 27-Jul-11 27-Jul-11 37 27-Jul-12 27-Jul-12 ... View more

simplified part data have2; set have1; by Material ; retain PrevValue ; if first.Material then PrevValue = Requirement ; if Requirement ne 0 then PrevValue = Requirement ; run; ... View more

You can use this code which gives your desired output Flow_typ Region Account_Status Amount St_date end_date External North Closed 500 1/01/2024 01/31/2024 External North Closed 500 3/01/2026 05/31/2026 External North Closed 600 2/01/2024 02/28/2024 Internal South Open 100 1/01/2020 01/31/2020 Internal South Open 200 2/01/2021 02/28/2021 Internal South Open 200 5/01/2030 07/31/2030   proc sort data = test1 out = test1; by Flow_typ Region Account_Status Amount St_date end_date ; run; /*array to compare previous vales to identify consecutive days*/ data test2; set test1; by Flow_typ Region Account_Status Amount ; array sdate(1); format sdate1 mmddyy10.; array edate(1); format edate1 mmddyy10.; if first.amount then do; i=1 ; sdate(1)=st_date; edate(1)=end_date; NewGroup=1; end; if i ne 1 then do; if st_date ne edate(1)+1 then do; sdate(1)=st_date; edate(1)=end_date; NewGroup=2; end; else if i ne 1 and st_date = edate(1)+1 then do; edate(1) = end_date; end; else NewGroup+1; end; i + 1; retain sdate1 edate1 ; drop i; run; Proc sql; Create table test3 as Select distinct Flow_typ, Region, Account_Status, Amount, min(sdate1) as St_date format mmddyy10., Max(edate1) as end_date format mmddyy10. from test2 group by Flow_typ, Region, Account_Status, Amount,NewGroup /*instead of NewGroup sdate1*/ ;Quit; ... View more

Are you looking for this? proc sql; select tx_id, amt, sum(amt)as req, count(tx_id) as tx_count from have group by account_id having sum(amt)>=9000 and count(tx_id)>1 order by tx_id ;quit; Output: tx_id amt req tx_count 1 1000 10000 10 2 1000 10000 10 3 1000 10000 10 4 1000 10000 10 5 1000 10000 10 6 1000 10000 10 7 1000 10000 10 8 1000 10000 10 9 1000 10000 10 10 1000 10000 10   ... View more

data table; input group $ rate ; datalines; A 35000 B 746 C 462158 D 9534 E 760449 A 31.523 B 30.64 C 12.701 D 24.339 E 18.9111 ; run; data want; set table; if scan(put(rate, best.),2,".") = '' then rate = put(rate, 12.); if scan(put(rate, best.),2,".") ne '' then rate = put(rate, 12.1); run; Output: group rate A 35000 B 746 C 462158 D 9534 E 760449 A 31.5 B 30.6 C 12.7 D 24.3 E 18.9 ... View more

You can try this: proc sql; create table t_1 as select * from t2 cross join (Select distinct Id from t1) ; quit; proc sql; create table t_2 as select a.Id, a.month_ , b.wealth from t_1 as a left join t1 as b on a.month_=b.month and a.id=b.Id order by a.ID, a.month_ ; quit;   Output T_2 ID month_ wealth 1 2201 10 1 2202 20 1 2203 30 1 2204 40 1 2205 . 1 2206 . 2 2201 50 2 2202 60 2 2203 70 2 2204 80 2 2205 90 2 2206 100 3 2201 110 3 2202 120 3 2203 . 3 2204 130 3 2205 140 3 2206 150 ... View more

  data TargetTable; do Id=1 to 10; output; end; run;   Proc sql; Select count(*) as count into :Count from TargetTable ;Quit; %put &count; %let subsetsize=3; %let tablecount=%sysfunc(ceil(%sysevalf(&count/&subsetsize))); %put &tablecount; %macro createtables; %let fo=1; %do i=1 %to &tablecount; data newtable&i; set TargetTable (obs = %sysevalf(&subsetsize+&fo-1) firstobs = &fo) ; run; %let fo=%sysevalf(&subsetsize+&fo); %end; %mend; %createtables;   Output: NewTable1 NewTable2 NewTable3 NewTable4 Id Id Id Id 1 4 7 10 2 5 8   3 6 9     ... View more

Create a district code table and then merge or join: data DistrictCodes; Input Districts $ DistrictCode ; datalines; Alachua 0 Baker 1 Bay 2 Bradford 3 Brevard 4 Broward 5 Calhoun 6 Charlotte 7 Citrus 8 Clay 9 Collier 10 Columbia 11 MiamiDade 12 DeSoto 13 Dixie 14 Duval 15 ; run; Proc sql; Create table flgrads as Select a.*, b.* from flgrads as a left join DistrictCodes as b on upcase(a.Districts)=upcase(b.Districts) ;Quit; ... View more

Hope this helps data Clients; input ID TechClients LegalClients ; datalines; 12345 54 35 87564 20 100 ; run; data ClientType; input CType$; datalines; Tech Legal ; run; Proc sql; Create table ClientsWithClientType as Select * from Clients cross join ClientType ;Quit; data Commission; input ID Commission CommissionType $; datalines; 12345 10000 Tech 12345 5000 Legal 87564 8000 Legal ; run; Proc sql; Create table Output as Select a.Id , case when b.Commission=. then 0 else a.TechClients end as TechClients , a.LegalClients , coalesce(b.Commission,0) as Commission , a.CType from ClientsWithClientType as a left join Commission as b on a.Id=b.Id and a.Ctype=b.CommissionType order by Id, Ctype desc ;Quit;   Output ID TechClients LegalClients Commission CType 12345 54 35 10000 Tech 12345 54 35 5000 Legal 87564 0 100 0 Tech 87564 20 100 8000 Legal ... View more