Requirement Gathering for report

[yash82]

Hi all,

I have to prepare an adhoc report like this

Type	Price1	Price2	Price3	Price4
Insurance	Val.table1	Val.table2	Val.table3	Val.table4
Catastrophe	Val.table2	Val.table4
Risk	Val.table1	Val.table2	Val.table3	Val.table4
Man Made	Val.table1	Val.table3
Natural	Val.table1	Val.table2	Val.table3	Val.table4
Health	Val.table2	Val.table4

what I am confused about is how to prepare the dataset for something like this. I have an approach in mind like to  Create temp tables using SQL joins and then use them something like this

proc sql;

create table temp1 as select a.val1,b.name from table1 left join comm_table b on a.key=b.key where b.name in ('Insurance,Risk,Man Made,Natural);

quit;

proc sql;

create table temp2 as select a.val2,b.name from tabel2 left join comm_table b on a.key=b.key where b.name in ('Insurance,Catastrophe,Risk,Health);

quit;

proc sql;

create table temp3 as select a.val3,b.name from table3 left join comm_table b on a.key=b.key where b.name in ('Insurance,Risk,Natural);

quit;

proc sql;

create table temp4 as select a.val4,b.name from table4 left join comm_table b on a.key=b.key where b.name in ('Insurance,Catastrophe,Risk,Natural);

quit;

data final;

set temp1 temp2 temp3 temp4;

run;

Am I thinking in correct direction??? because this set statement will produce the missing values for remaining variables so I am in fix to how to create the final dataset. Can somebody come up with better approach. May be just one SQL statement and then how to go about producing report like this. Should I go for PROC Report or in this case one should go for data _null_ reporting.

[art297]

I think you have to provide more info.  What are tables 1, 2, 3 and 4 and what is in comm_table?  Unless I am missing something, it looks like a single proc sql step with a case statement might suffice, but one can only guess without knowing what is actually involved.

[yash82]

Hi Art  Please find Tables as below and SQL statements I think we should use

Table1			Table2			Table3			Table4
Id1	Price1		Id2	Price2		Id3	Price3		Id4	Price4
12	100		130	500		9	900		13	20
24	300		131	600		24	1100		45	52
45	400		132	700		114	567		56	63
72	882		135	44					777	784

Common  Table
Name	Key1	Key2	Key3	Key4
Insurance	12	130	9	13
Catastrophe	32	131	10	45
Risk	24	132	11	56
Man Made	45	133	12	68
Natural	72	134	114	777
Health	74	135	128	754

proc sql;

create table temp1 as select a.price1,b.name from table1 left join comm_table b on a.id1=b.key1 where b.name in ('Insurance,Risk,Man Made,Natural);

quit;

proc sql;

create table temp2 as select a.price2,b.name from tabel2 left join comm_table b on a.id2=b.key2 where b.name in ('Insurance,Catastrophe,Risk,Health);

quit;

proc sql;

create table temp3 as select a.price3,b.name from table3 left join comm_table b on a.id3=b.key3 where b.name in ('Insurance,Risk,Natural);

quit;

proc sql;

create table temp4 as select a.price4,b.name from table4 left join comm_table b on a.id4=b.key 4where b.name in ('Insurance,Catastrophe,Risk,Natural);

quit;

Thanks

[art297]

Unless I am missing something, your data tables and desired output (as shown in your initial post) don't match.

Given the data you provided I only find the following:

Type	Price1	Price2	Price3	Price4
Insurance	Val.table1	Val.table2	Val.table3	Val.table4
Catastrophe	Val.table2	Val.table4
Risk	Val.table1	Val.table2	Val.table4
Man Made	Val.table1
Natural	Val.table1	Val.table3	Val.table4
Health	Val.table2

Let me know if there is more data, or if the above table is actually correct, or if I simply don't understand what you are trying to accomplish.

[yash82]

Actually following 5 are my tables

Table1
Id1 Price1
12 100
24 300
45 400
72 882

Table2
Id2 Price2
130 500
131 600
132 700
135 44

Table3
Id3 Price3
9 900
24 1100
114 567

Table4
Id4 Price4
13 20
45 52
56 63
777 784

Common  Table
Name Key1 Key2 Key3 Key4
Insurance 12 130 9 13
Catastrophe 32 131 10 45
Risk 24 132 11 56
Man Made 45 133 12 68
Natural 72 134 114 777
Health 74 135 128 754

[art297]

Those are the same tables that you posted previously.

In your original post you indicated that the last line of your desired table, the one labeled "Health", had values for price1 and price2, namely val.table2 and val.table4.

Where do those two figures come from given the data you supplied?

[yash82]

Hey Art, I am sorry for the typeo. What I meant by val.table2 is value of price2 for Health from table2 in a nutshell it should be value(price2) . The first post was just hurried one. So my final report should be

Type	Price1	Price2	Price3	Price4
Insurance	100	500	900	20
Catastrophe	500	20
Risk	300	600	.	63
Man Made	400	.
Natural	882	.	567	784
Health	.	44	.	.

so in my price1 for Insurance val.table1 means value off price1 from table1 where table1.id1=commontable.key1. Thanks a lot for going all the way out.

[art297 · Accepted solution]

I still get different results than your latest example, and I'm certain that the following can be optimized, but I think that it achieves what you are trying to do:

data Table1;

  input Id1 Price1;

  cards;

12          100

24          300

45          400

72          882

;

data Table2;

  input Id2 Price2;

  cards;

130          500

131          600

132          700

135          44

;

data Table3;

  input Id3 Price3;

  cards;

9 900

24          1100

114          567

;

data Table4;

  input Id4 Price4;

  cards;

13          20

45 52

56          63

777          784

;

data comm_Table;

  informat Name $11.;

  input Name & Key1-Key4;

  cards;

Insurance  12          130          9          13

Catastrophe  32          131          10          45

Risk  24          132          11          56

Man Made  45          133          12          68

Natural  72          134          114          777

Health  74          135          128          754

;

proc sql;

  create table temp1 as

    select a.price1,b.name from table1 a

      left join comm_table b

        on a.id1=b.key1

          where b.name in ("Insurance","Risk","Man Made","Natural");

  create table temp2 as

    select a.price2,b.name from table2 a

      left join comm_table b

        on a.id2=b.key2

          where b.name in ("Insurance","Catastrophe","Risk","Health");

  create table temp3 as select a.price3,b.name from table3 a

    left join comm_table b

      on a.id3=b.key3

        where b.name in ("Insurance","Risk","Natural");

  create table temp4 as

    select a.price4,b.name from table4 a

      left join comm_table b

        on a.id4=b.key4

          where b.name in ("Insurance","Catastrophe","Risk","Natural");

quit;

proc format;

  value $types

  "Insurance"=1

  "Catastrophe"=2

  "Risk"=3

  "Man Made"=4

  "Natural"=5

  "Health"=6

  ;

run;

data final;

  set temp1-temp4;

  type=put(name,$types.);

run;

proc sql;

  create table want as

    select distinct type,name,

           max(price1) as price1,

           max(price2) as price2,

           max(price3) as price3,

           max(price4) as price4

       from final

         group by type

           order by type

  ;

quit;

[Tom]

I still do not understand what you want.  It looks to me like you have four lookup tables for four prices.  (no idea what the four different prices represent)  You could easily build a single SQL query using LEFT JOINs to combine the five tables into one.

data table1 ;
  input id1 price1 @@ ;
cards;
12 100 24 300 45 400 72 882
run;
data table2 ; input id2 price2 @@;
cards;
130 500 131 600 132 700 135 44
run;
data table3;
  input id3 price3 @@;
cards;
9 900 24 1100 114 567
run;
data table4 ;
  input id4 price4 @@;
cards;
13 20 45 52 56 63 777 784
run;
data common;
  length name $15 ;
  input name & key1-key4 ;
cards;
Insurance  12 130 9 13
Catastrophe  32 131 10 45
Risk  24 132 11 56
Man Made  45 133 12 68
Natural  72 134 114 777
Health  74 135 128 754
run;
proc sql noprint ;
  create table want as
   select c.name
        , t1.price1
        , t2.price2
        , t3.price3
        , t4.price4
   from common c
    left join table1 t1
      on c.key1=t1.id1
    left join table2 t2
      on c.key2=t2.id2
    left join table3 t3
      on c.key3=t3.id3
    left join table4 t4
      on c.key4=t4.id4
   where c.name in ('Insurance','Catastrophe','Risk','Natural')
  ;
quit;
proc print; run;

Obs    name           price1    price2    Price3    Price4 
 1     Insurance        100       500       900        20
 2     Catastrophe        .       600         .        52
 3     Risk             300       700         .        63
 4     Natural          882         .       567       784

Your output seems to have the values shifted to the left instead of staying in the variables that represent the original lookup table that they came from.  Why?

[yash82]

Thanks a lot Art and Tom... Both the methods solved my purpose....