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.
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;
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.
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
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.
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
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?
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.
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;
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?
Thanks a lot Art and Tom... Both the methods solved my purpose....
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.