Hello I have the following question. I have a data set called 'tbl1' which contain fields: x,y1,y2. Sometimes there another data set called "recovery_tbl' which contain fields: x,w,y1,y2 If in specific field all values are missing(null values in all columns) then If a recovery data set exists it will gather the values from there. Data tbl1; input ID y1 y2; cards; 1 10 . 2 20 . 3 30 . 4 40 . 5 50 . ; Run; Data recovery_tbl; input ID w y1 y2; cards; 1 5 10 100 2 6 20 200 3 7 30 300 4 8 40 400 5 9 50 500 ; Run; PROC SQL; create table tbl1_new as select a.ID,a.Y1,coalesce(a.Y2,b.Y2) as Y2 from tbl1 as a left join recovery_tbl as b on a.ID=b.ID ; QUIT; Can anyone how to perform this conditional merge. The merge need to be done only if 2 conditions happens: 1-There is a field that all values are null (missing values) 2- A recover table exists ... View more

Hello Please see following code. I need to create one more row in the output with percent of each column's total from grand total. 811/1901=43% 1090/1901=57% 1901/1901=100% I guess that we cannot do it with proc report. Can anyone show another way to do it with proc tabulate?     Proc report data=sashelp.class nowd; columns age sex,(weight=weightsum) weight=TotalSum; define age/ group ; define weight / sum; define sex/across 'sex' order=formatted ; define weightsum / sum format=comma12. ; define TotalSum / sum format=comma12. 'All sex'; rbreak after/ summarize; run; ... View more

Hello I want create report using proc report. Please see the code. I want to add one more column of PCT from total for each team. For category a we need to get 100/550=18% For category a we need to get 450/550=82% Data tbl; input ID Team $ Country $ sales; cards; 1 a UK 10 2 a UK 20 3 a UK 30 4 a USA 40 5 b UK 50 6 b UK 60 7 b USA 70 8 b USA 80 9 b USA 90 10 b USA 100 ; run; Proc report data=tbl nowd; columns Team Country,(sales=salespctsum) ; define Team/ group ; define Country/across order=formatted ; define salespctsum / pctsum format=percent9.1 ; rbreak after/ summarize; run; ... View more

Hello Is it possible to calculate conditional sum in proc report? It means that I want to calculate sum for different populations. Please see the example by proc sql. The target is to create the same output via proc report.   Data tbl; input ID Ind1 $ Ind2 $ y; cards; 1 a T1 10 2 a T1 20 3 a T1 30 4 a T2 40 5 a T2 50 6 a T3 60 7 a T4 70 8 b T1 80 9 b T1 90 10 b T2 100 11 b T3 110 ; Run; PROC SQL; create table Result as select sum(case when Ind1='a' and Ind2='T1' then y else 0 end) as Sum_Y_a_T1, sum(case when Ind1='a' and Ind2='T2' then y else 0 end) as Sum_Y_a_T2, sum(case when Ind1='a' and Ind2 not in('T1' ,'T2') then y else 0 end) as Sum_Y_a_No_T1_No_T2, sum(case when Ind1='b' and Ind2='T1' then y else 0 end) as Sum_Y_b_T1, sum(case when Ind1='b' and Ind2='T2' then y else 0 end) as Sum_Y_b_T2, sum(case when Ind1='b' and Ind2 not in('T1' ,'T2') then y else 0 end) as Sum_Y_b_No_T1_No_T2 from tbl ; QUIT; ... View more

Hello I want to use proc report. I want to have 7 fields in required report. required fields are: TEAM Number of customers in 1806 Sum of Y in 1806 Difference of Sum of Y between 1806 and 1805 PCT difference of Sum of Y between 1806 and 1805 Difference of Sum of Y between 1806 and 1712 PCT difference of Sum of Y between 1806 and 1712   My problem is that I am getting also fields that I don't want (because of using across...)     DATA tbl1; input ID mon Team $ Y; cards; 1 1806 a 10 2 1806 a 11 3 1806 b 12 4 1806 a 13 5 1806 b 14 6 1806 b 15 7 1806 a 16 1 1805 a 10 2 1805 a 11 3 1805 b 12 4 1805 b 13 5 1805 a 14 1 1712 a 8 2 1712 a 9 3 1712 b 10 5 1712 b 11 6 1712 a 12 7 1712 a 13 ; run; Proc report data=tbl1; columns Team mon,(Y=Yn Y=Ysum ) ('Comparison to previous month' diff_Y_From_PrevMon PCT_diff_Y_From_PrevMon) ('Comparison to last DEC' diff_Y_From_LastDEC PCT_diff_Y_From_LastDEC) ; define Team/ group order=data 'Team'; define mon/across order=data 'Month'; define Yn / n 'Customers' format=comma12. ; define Ysum / sum 'SUM_Y' format=comma12.; define diff_Y_From_PrevMon/ computed 'differnce_Y'; compute diff_Y_From_PrevMon; diff_Y_From_PrevMon =_c3_-_c5_; endcomp ; define PCT_diff_Y_From_PrevMon/ computed format=percentn9.1 'PCT_differnce_Y'; compute PCT_diff_Y_From_PrevMon; PCT_diff_Y_From_PrevMon =(_c3_-_c5_)/_c5_; endcomp ; define diff_Y_From_LastDEC/ computed 'differnce_Y'; compute diff_Y_From_LastDEC; diff_Y_From_LastDEC =_c3_-_c7_; endcomp ; define PCT_diff_Y_From_LastDEC/ computed format=percentn9.1 'PCT_differnce_Y'; compute PCT_diff_Y_From_LastDEC; PCT_diff_Y_From_LastDEC =(_c3_-_c7_)/_c7_; endcomp ; rbreak after/ summarize; run;   DATA tbl1; input ID mon Team $ Y; cards; 1 1806 a 2 1806 a 3 1806 b 4 1806 a 1 1805 a 2 1805 a 3 1805 c 4 1805 b 5 1805 a 1 1712 a 2 1712 a 3 1712 c 5 1712 c 6 1712 a 7 1712 a ; run; ... View more

Hello In the following program I want to display 4 column with percent format (percent9.1) pctn pctsum Diff_PCT_Y Diff_PCT_cust     I want also to ask why  pctn  and pctsum are not in define statements? Data tbl1806; input ID team $ Y ; cards; 1 817 10 2 817 20 3 818 30 4 828 40 5 818 50 6 890 60 7 890 70 8 890 80 9 890 90 10 890 100 ; Run; Data tbl1712; input ID team $ Y; cards; 1 817 15 2 817 25 3 818 35 4 828 45 5 818 55 6 890 65 7 818 70 11 818 110 12 818 120 ; Run; data combined; set tbl1806 tbl1712 indsname=ds; length mon $ 5; mon=substr(scan(ds,2,'.'),4); run; proc format library=work; value $type (multilabel notsorted) '817' = '817' '818' = '818' '828' = '828' '817','818','828' = '817_818_828' ' '='All' other = 'not 817_818_828' ; run; Proc report data=combined nowd; columns team mon,(n pctn sum pctsum),y diff_cust Diff_Y Diff_PCT_cust Diff_PCT_Y; define team/ group mlf format=$type. preloadfmt order=data 'צוות'; define mon/across order=data 'חודש'; define diff_cust/ computed; compute diff_cust; diff_cust =_c2_-_c6_; endcomp ; define Diff_PCT_cust/ computed; compute Diff_PCT_cust; Diff_PCT_cust=_C3_-_C7_; endcomp ; define Diff_Y/ computed; compute Diff_Y; Diff_Y=_C4_-_C8_; endcomp ; define Diff_PCT_Y/ computed; compute Diff_PCT_Y; Diff_PCT_Y=_C5_-_C9_; endcomp ; rbreak after/ summarize; run;   ... View more