The SAS Output Delivery System and reporting techniques

Requirement Gathering for report

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

Requirement Gathering for report

Hi all,

I have to prepare an adhoc report like this

TypePrice1Price2Price3Price4
InsuranceVal.table1Val.table2Val.table3

Val.table4

CatastropheVal.table2Val.table4

RiskVal.table1Val.table2Val.table3Val.table4
Man MadeVal.table1Val.table3

NaturalVal.table1Val.table2Val.table3Val.table4
HealthVal.table2Val.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.


Accepted Solutions
Solution
‎01-01-2012 03:26 PM
PROC Star
Posts: 7,363

Re: Requirement Gathering for report

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;

View solution in original post


All Replies
PROC Star
Posts: 7,363

Requirement Gathering for report

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.

Contributor
Posts: 25

Re: Requirement Gathering for report

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

Table1
Table2
Table3
Table4
Id1Price1
Id2Price2
Id3Price3
Id4Price4
12100
130500
9900
1320
24300
131600
241100
4552
45400
132700
114567
5663
72882
13544



777784
Common  Table
NameKey1Key2Key3Key4
Insurance12130913
Catastrophe321311045
Risk241321156
Man Made451331268
Natural72134114777
Health74135128754

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

PROC Star
Posts: 7,363

Re: Requirement Gathering for report

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:

TypePrice1Price2Price3Price4
InsuranceVal.table1Val.table2Val.table3

Val.table4

CatastropheVal.table2Val.table4

RiskVal.table1Val.table2Val.table4
Man MadeVal.table1


NaturalVal.table1Val.table3Val.table4
HealthVal.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.

Contributor
Posts: 25

Re: Requirement Gathering for report

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

PROC Star
Posts: 7,363

Re: Requirement Gathering for report

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?

Contributor
Posts: 25

Re: Requirement Gathering for report

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 Price1Price2Price3Price4
Insurance10050090020
Catastrophe50020
Risk300600.63
Man Made400.
Natural 882.567784
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.

Solution
‎01-01-2012 03:26 PM
PROC Star
Posts: 7,363

Re: Requirement Gathering for report

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;

Super User
Super User
Posts: 6,499

Re: Requirement Gathering for report

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?

Contributor
Posts: 25

Re: Requirement Gathering for report

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 285 views
  • 3 likes
  • 3 in conversation