Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 141
Accepted Solution

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

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

 

Capture.PNG

 

Transpose Data Set example of where I overrode the format.

 

Capture1.PNG

 

Results of transposed data set

 

Capture2.PNG


Accepted Solutions
Solution
‎01-21-2016 09:23 AM
Frequent Contributor
Posts: 141

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

@Reeza I have quickly tried your idea and it seems to work.  My SQL unions have also resolved the issue.  I appreciate everyone's help.  This thread provides several ways to work through this.

View solution in original post


All Replies
Super User
Posts: 19,157

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

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.

Super User
Posts: 11,134

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

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.

Frequent Contributor
Posts: 141

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

@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.

Super User
Posts: 19,157

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

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. 

Super User
Posts: 11,134

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

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.

Frequent Contributor
Posts: 141

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

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

Super User
Posts: 11,134

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

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;
Frequent Contributor
Posts: 141

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

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.

Super User
Posts: 9,867

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

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

Frequent Contributor
Posts: 141

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

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.

Frequent Contributor
Posts: 141

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

[ Edited ]

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.

 

Capture4.PNG

 

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.

Super User
Posts: 19,157

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

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

 

 

Super User
Posts: 19,157

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

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;
Super User
Posts: 9,867

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

[ Edited ]

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;
☑ This topic is solved.

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

Discussion stats
  • 15 replies
  • 1091 views
  • 4 likes
  • 4 in conversation