for anyone being SAS developer, I thought basic problem definition with little illustrations, should have been enough to understnad the problem at hand. However, I will try to explain again: Yes the user defined formats are defined in my session and a list of columns and their user defined formats is applied on the main table data with obvious statement:   format &full_format_list; ---where, full_format_list is something like: (c1 X. c5 Y. c47 Z. ... etc)   Output is our table that looks something like the one attached: (note: it is just the small snapshot of the table containing huge data and many comuns).   format tables l1,l5,l47 etc have been created using : proc format cntlout = &outvals (keep = fmtname start label   So my problem is : I need to join the main table with each of these format tables to gather the count and balance for each label value of the formats.   something like this: select "&var_cat" as Category length=50,t2.LABEL as Categories,count(t1.acc_id) as N,t2.POU_DT,sum(BALANCE_AMT) as Balance from main_table t1 RIGHT OUTER JOIN  l&i. t2 ON strip(put(t1.&&VARS&i.,32.))=t2.LABEL and t1.POU_DT=t2.pou_dt group by t2.pou_dt,t2.FMTNAME,t2.LABEL order by t2.LABEL,t2.pou_dt asc;     but the join fails as operands on either side of = are of different formats for obvious reasons. So I need the entire main_table to be in character format. If there is any way other than proc transpose applied twice, please let me know. Thanks in advance!   ... View more

I have tables for each user defined formats as: 1)say l1: format_name, start, label X 0 =0 X 60 >60,<120 Similarly, l5,l47,l77....etc 2) I have list of columns on which above formats are applied on main table: c1 c5 c47 c77.... Etc Ie, table column c1 has been applied with l1 format etc. Now I have main table produced after applying above formats as : 3) say tab: id, c1, c2, c3,...................., c100 1 >60,<120 abc ............ 4) I need to join table tab with all format tables to collect the counts of ids and sum of balances. So I intend to join on label values. ... View more

sorry in output table i am getting as of result of proc report does not have x1 populated for all a1,a2,a3,a4,a5 and similarly for x2. I need to do that as well. ... View more

my source data is a table with data similar to below: fmtname label  date        balance x1       a1    20/03/2019   10 x1       a2    20/03/2019   20 x1       a3    .            . x1       a4    25/03/2019   30 x1       a5    .            . x2       b1    25/03/2019   40 x2       b2    .            . x2       b3    20/03/2019   50   now i need a table as below:                                         Balances on Dates Format   Format           20/03/2019  25/03/2019 name     value label      Balance        Balance      x1           a1                     10                 0 x1           a2                     20                 0 x1           a3                     0                   0 x1           a4                     0                  30 x1           a5                     0                   0 x2           b1                     0                   40 x2           b2                     0                   0 x2           b3                     50                 0     below was the code i used to generate such a table but as source data as "." in date and balance columns for some rows, I get that "." column also listed as below:   proc report data=alldata colwidth=10 nowindows missing ;   column FMTNAME LABEL dt,balance; define FMTNAME / group order=data; define LABEL / display order = data; define dt / across 'Balances on Dates'; define balance / analysis sum ; break after FMTNAME / skip;                                           Balances on Dates Format   Format           .             20/03/2019  25/03/2019 name     value label      Balance  Balance        Balance      x1           a1                    .                10                 0 x1           a2                     .               20                 0 x1           a3                     .                0                   0 x1           a4                     .                0                  30 x1           a5                     .                0                   0 x2           b1                     .                0                   40 x2           b2                     .                0                   0 x2           b3                     .                50                 0 ... View more

No it cannot be removed as grouping is on date and on that group sum(balance_amount) is calculated. ... View more

proc report data=alldata colwidth=10 nowindows missing ;   column FMTNAME LABEL dt,balance; define FMTNAME / group order=data; define LABEL / display order = data; define dt / across 'Balances on DTs';   define balance_amt / analysis sum ; break after FMTNAME / skip;   Above is the code and data is a table containing FMTNAME LABEL dt,balance where dt field is '.' for FMTNAME LABEL combination as for that date this combination related data is not present and hence even the balance is '.' for the same.   ... View more