Losing formatting from original data set when using a transpose task?

[elwayfan446]

Hello!

I am trying to use a transpose task and when I do, I am losing the format of the values from the original data set.  I have gone into the transposed data set and manually changed the formats but they are not sticking.  Any ideas what I am doing wrong?

Original Data Set

Transpose Data Set example of where I overrode the format.

Results of transposed data set

[Reeza]

A column can only have one format, so yes, when you transpose you'll lose the dollar formats and they'll all take one format. 

Also,  I don't think changes to the viewer are maintained, you have to change the formats via code or some other method in EG.

[ballardw]

I can't read the middle picture at all.

Comment on the data:  It appears you have taken columns that refer to counts and dollar amounts and put them into a single column.

I would find that a very dangerous thing to do in a generic sense as the values are not going to be easy to use. What would the sum, mean or standard deviation of that combined column mean?

What are you going to do with that data next?

If you are going to do something like this routinely learn to use proc datasets to change properties of datasets in place.

[elwayfan446]

@ballardw for this purpose only I am just wanting to generate an html file of this data to embed into an email.  My next step was to use code to do that that I found here unless I can come up with a better way.  EG exports it as an attachment.  Otherwise I have no plans to use transpose very often.  I simply wanted it to fit in the email more cleanly.

@Reeza I completely forgot about not having multipe data types in the same column.

[Reeza]

If it's for presentation purposes, you can covert it to a text field and apply the correct format. But it's a pretty manual tedious process. 

[ballardw]

It may be that a report procedure such as Proc Report or Tabulate would be a better idea than transposing a dataset. Then formats and appearance options can be set.

[elwayfan446]

Cool.  I will give these ideas a try and report back.  You guys are awesome.

[ballardw]

Here is an example that uses Proc Tabulate to Stack and nest some values using a dataset you should have available:

tabulate data=sashelp.cars;
   class make type;
   var msrp  enginesize horsepower;
   table make *(
                 msrp * (max*f=dollar10.2 min*f=dollar10.2 mean*f=dollar10.2)
                 enginesize *(min*f=best4. max*f=best4.)
                 horsepower *(max*f=6.1 min*f=6.1 mean*f=7.2)
                ),
          type
          /misstext=' ';
run;

[elwayfan446]

I am still struggling with this.  None of the options I have tried are working like I need it to.  Maybe if I show you the data I am trying to work with you would have another suggestion for me.  I attached an excel workbook with a "have" and "want" tab.  It could be that your suggestions would do this but I just haven't been able to get them to work.

[Ksharp]

Yes. proc transpose somtimes will do that . It depends on how you transpose your data.

Here is MERGE skill proposed by me , Matt and Arthur.T which can retain the original format from dataset.

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

[elwayfan446]

Thanks @Ksharp.  I am trying to figure out how to make this work like I need it to for my example on the "have" tab of the attached workbook.  I am not sure I understand how to apply this to what I need but I will try looking at it again in the morning with fresh eyes.

[elwayfan446]

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.

[Reeza]

As a giant hint, you're much more likely to get working code when you post data as a data step.  

[Reeza]

Use an array, VLABEL and PUTN

data have;
metric=23456; format metric comma12.;
data2=25678832; format data2 dollar21.;
data3=4; format data3 8.;
data4=0.1; format data4 percent8.1;
data5=0.9; format data5 8.;

label metric="Data 1" data2="Metric 1" data3="Test ME!!!" data4='Data 4' data5='This is awesome';
run;

data want;
set have;

length var_label var_char  $50.;
format var_label var_char  $50.;

array tp(*) metric data2-data5;

do i=1 to dim(tp);
var_format=vformat(tp(i));
var_char=putn(tp(i), var_format);
var_label=vlabel(tp(i));
output;
end;

keep var_char var_label;
run;

[Ksharp]

I have some time to review your question. You are doing a special transposing data.

data have;
infile cards dsd dlm='09'x;
input (LOANS_PRIOR_PIPELINE	PRIOR_PIPELINE_UPB	LOANS_ADDED	LOANS_ADDED_UPB	LOANS_FUNDED	LOANS_FUNDED_UPB	LOANS_DROPPED	LOANS_DROPPED_UPB	UPB_CHANGED	NET_PIPELINE_CHANGE	NET_PIPELINE_CHANGE_UPB	CURRENT_PIPELINE	CURRENT_PIPELINE_UPB) (: dollar32.);
cards;
828	$167,763,474	119	$30,995,015	150	$43,876,489	-3	-$492,500	-$106,669	-34	-$13,373,974	794	$154,282,831
;
run;
proc transpose data =have out=temp;run;
data temp;
 set temp;
 length id name $ 20;
 if findw(_name_,'UPB','_ ','i') then id='Amount';
  else id='Count';
 name=prxchange('s/^LOANS_|_UPB$//i',-1,strip(_name_));
run;
proc sort data=temp;by name;run;
proc transpose data=temp out=want;
by name;
id id;
var col1;
run;
data want;
 set want;
 format Amount dollar32.;
 drop _name_;
run;