topic Re: Requirement Gathering for report in ODS and Base Reporting

Requirement Gathering for report

yash82 — Sat, 31 Dec 2011 19:36:02 GMT

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.

Requirement Gathering for report

art297 — Sat, 31 Dec 2011 19:57:22 GMT

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.

Re: Requirement Gathering for report

yash82 — Sun, 01 Jan 2012 07:36:23 GMT

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

Re: Requirement Gathering for report

art297 — Sun, 01 Jan 2012 18:20:50 GMT

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.

Re: Requirement Gathering for report

yash82 — Sun, 01 Jan 2012 19:31:01 GMT

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

Re: Requirement Gathering for report

art297 — Sun, 01 Jan 2012 19:43:46 GMT

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?

Re: Requirement Gathering for report

yash82 — Sun, 01 Jan 2012 20:16:02 GMT

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.

Re: Requirement Gathering for report

art297 — Sun, 01 Jan 2012 20:26:28 GMT

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;

Re: Requirement Gathering for report

Tom — Sun, 01 Jan 2012 23:01:01 GMT

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?

Re: Requirement Gathering for report

yash82 — Wed, 04 Jan 2012 17:46:40 GMT

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