Ok, I played around a little more and found a solution that works for me. Disclaimer... it wouldn't be the most efficeint solution with a lot of data but for this small amount of variables it works pretty well. I utilized proc sql and subqueries to pull the information. proc sql;
select distinct
'Prior Day Pipeline' as Metric,
(SELECT TOTALS1 from FLOWFRED.FMFLOW_EMAILSNAP_PIVOT p2 where p2.Source = 'LOANS_PRIOR_PIPELINE') as Count,
(SELECT TOTALS1 from FLOWFRED.FMFLOW_EMAILSNAP_PIVOT p2 where p2.Source = 'PRIOR_PIPELINE_UPB') as UPB FORMAT=DOLLAR20.,
1 as SortOrder
from FLOWFRED.FMFLOW_EMAILSNAP_PIVOT p
union
select distinct
'Committed to Pipeline' as Metric,
(SELECT TOTALS1 from FLOWFRED.FMFLOW_EMAILSNAP_PIVOT p2 where p2.Source = 'LOANS_ADDED') as Count,
(SELECT TOTALS1 from FLOWFRED.FMFLOW_EMAILSNAP_PIVOT p2 where p2.Source = 'LOANS_ADDED_UPB') as UPB FORMAT=DOLLAR20.,
2 as SortOrder
from FLOWFRED.FMFLOW_EMAILSNAP_PIVOT p
union
select distinct
'Funded' as Metric,
(SELECT TOTALS1 from FLOWFRED.FMFLOW_EMAILSNAP_PIVOT p2 where p2.Source = 'LOANS_FUNDED') as Count,
(SELECT TOTALS1 from FLOWFRED.FMFLOW_EMAILSNAP_PIVOT p2 where p2.Source = 'LOANS_FUNDED_UPB') as UPB FORMAT=DOLLAR20.,
3 as SortOrder
from FLOWFRED.FMFLOW_EMAILSNAP_PIVOT p
union
select distinct
'Removed from Pipeline' as Metric,
(SELECT TOTALS1 from FLOWFRED.FMFLOW_EMAILSNAP_PIVOT p2 where p2.Source = 'LOANS_DROPPED') as Count,
(SELECT TOTALS1 from FLOWFRED.FMFLOW_EMAILSNAP_PIVOT p2 where p2.Source = 'LOANS_DROPPED_UPB') as UPB FORMAT=DOLLAR20.,
4 as SortOrder
from FLOWFRED.FMFLOW_EMAILSNAP_PIVOT p
union
select distinct
'UPB Changed' as Metric,
(.) as Count,
(SELECT TOTALS1 from FLOWFRED.FMFLOW_EMAILSNAP_PIVOT p2 where p2.Source = 'UPB_CHANGED') as UPB FORMAT=DOLLAR20.,
5 as SortOrder
from FLOWFRED.FMFLOW_EMAILSNAP_PIVOT p
union
select distinct
'Net Pipeline Change' as Metric,
(SELECT TOTALS1 from FLOWFRED.FMFLOW_EMAILSNAP_PIVOT p2 where p2.Source = 'NET_PIPELINE_CHANGE') as Count,
(SELECT TOTALS1 from FLOWFRED.FMFLOW_EMAILSNAP_PIVOT p2 where p2.Source = 'NET_PIPELINE_CHANGE_UPB') as UPB FORMAT=DOLLAR20.,
6 as SortOrder
from FLOWFRED.FMFLOW_EMAILSNAP_PIVOT p
union
select distinct
"Today's Pipeline" as Metric,
(SELECT TOTALS1 from FLOWFRED.FMFLOW_EMAILSNAP_PIVOT p2 where p2.Source = 'CURRENT_PIPELINE') as Count,
(SELECT TOTALS1 from FLOWFRED.FMFLOW_EMAILSNAP_PIVOT p2 where p2.Source = 'CURRENT_PIPELINE_UPB') as UPB FORMAT=DOLLAR20.,
7 as SortOrder
from FLOWFRED.FMFLOW_EMAILSNAP_PIVOT p
order by SortOrder;
quit; Here was the final dataset. I created the "labels" for the rows by hard coding them. I then used subqueries to pull the variable I needed from the proper observation. I did this for each observation and created the dataset by using a union for each observation. I also added a sort order number for each union to ensure the rows sorted the way I wanted them to appear in my new dataset. I am not giving up on the paper that @Ksharp pointed me to. I am sure there is a more efficient solution using that method. I am going to sit down and study it as soon as I get time. Thanks to all of you for your help.
... View more
