BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
elwayfan446
Barite | Level 11

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

1 ACCEPTED SOLUTION

Accepted Solutions
elwayfan446
Barite | Level 11

@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

15 REPLIES 15
Reeza
Super User

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
Super User

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
Barite | Level 11

@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
Super User

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
Super User

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
Barite | Level 11

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

ballardw
Super User

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
Barite | Level 11

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
Super User

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
Barite | Level 11

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
Barite | Level 11

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.

Reeza
Super User

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

 

 

Reeza
Super User

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
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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