Help using Base SAS procedures

Proc Transpose Help - Order of transposed columns

Accepted Solution Solved
Reply
Regular Contributor
Posts: 212
Accepted Solution

Proc Transpose Help - Order of transposed columns

[ Edited ]

Hi. I have a Proc Transpose (see fingure 1) that ends up moving date style named variables to be column names (see figure 2). Right now the column names are not in any particular order.

 

My problem is that I need to order the column variables in a particular order when I Proc Print the dataset.  And each day I run the program I could have a different range of dates, so I can't hardcode an order.  

 

I need 'Total' first, then most recent date to oldest date, and finally the 'Null' column to be last.  Is there anyway I can achieve this?

 

Rule_Order

Rule_NM

Total

_04_11_2016

...more dates

_03_24_2016

Null

 

This is my existing Proc Transpose:

 

proc transpose data=FinalData out=FinalData (drop=_name_);
by rule_nm rule_order;
id ad_dt_char;
var n;
run;

This is my existing output:

 

RULE_NM	                        RULE_
                                ORDER   TOTAL   _04_01_2016     _04_05_2016     NULL    _04_08_2016
TOTAL BIDS VOLUME SAMPLED       999.3   54504   23024           1092            493     143

 

 


Accepted Solutions
Solution
‎04-12-2016 05:15 PM
Super User
Super User
Posts: 7,079

Re: Proc Transpose Help - Order of transposed columns

Posted in reply to buechler66

Proc transpose creates the columns as it sees new values of the ID variable. So by putting at least one example of every possible date in the first BY group (and in the right order) then it creates the columns in the right order.  Then the WHERE= dataset option deletes the extra group from the output.  Consider a  simple example.

 

data have ;
   input gender $ year count @@;
cards;
F 2003 10 F 2005 11 M 2002 12 M 2003 13
;;;;
proc transpose data=have out=want ;
  by gender ;
  id year ;
  var count;
run;

This will result in columns GENDER _2003 _2005 _2002.

But if I add an extra set of records that before the F group that has YEAR in the order 2002,2003,2005 then the columns in the output dataset will be created in that order.

View solution in original post


All Replies
Super User
Posts: 19,877

Re: Proc Transpose Help - Order of transposed columns

Posted in reply to buechler66

What do your dates currently look like? It looks like you've created a character column? Will that character column sort naturally ie alphabetically? If not can you change it so it does?

 

Then it becomes trivial to include the following in your proc print:

 

var Total date1-daten null;

 

Regular Contributor
Posts: 212

Re: Proc Transpose Help - Order of transposed columns

[ Edited ]

They are a char type because I needed to have columns named 'Total' and 'Null' coming out of the Proc Transpose.  I also have the original ad_dt date variables available in the dataset, but the Total and Null columns are missing values.  This is why I coverted them to ad_dt_char and recoded them to 'Total' and 'Null'.

 

The ad_date_char values values are coverted in a previous datastep:

 

ad_dt_char=PUT(datepart(ad_dt), mmddyy10.); * Convert to a char value so we can recode missing values to text values of Null and Total ;

Super User
Posts: 19,877

Re: Proc Transpose Help - Order of transposed columns

Posted in reply to buechler66

So, mmddyy will never sort properly. If you use yymmdd it will sort properly.

You can use labels to have it display whichever way you want it. 

 

My thought would then be to do the following, assuming you switched to DYYMMDD format for your date, so that it would be 

D20160104 D20160105 etc..

You need the D because SAS variables cannot start with a number. 

 

proc sql;
select catt("D", put(min(date), yymmdd10.)) , 
catt("D", put(max(date), yymmdd10.)) into : min_date, max_date
from have;
quit;

proc print data=report;
var Total &min_date - &max_date null;
run;

Super User
Posts: 5,518

Re: Proc Transpose Help - Order of transposed columns

[ Edited ]
Posted in reply to buechler66

To some extent, you can reorder the variables after the fact:

 

data want;

if 0 then do;

   set FinalData (keep=Rule_Order);

   set FinalData (keep=Rule_Nm);

   set FinalData (keep=Total);

   Set FinalData (keep=_: );

end;

set FinalData;

run;

 

This won't re-arrange the dates, but everything else would be in order.  A full solution would become more complex, likely involving macro language.

Regular Contributor
Posts: 212

Re: Proc Transpose Help - Order of transposed columns

Posted in reply to Astounding
Can you explain the 'if 0' part of the code? I'm not sure what is 0?
Super User
Posts: 5,518

Re: Proc Transpose Help - Order of transposed columns

Posted in reply to buechler66

SAS considers 0 to be false.  The statement could just as easily have read:

 

if 5=4 then do;

 

It's just a false condition.  So the SET statements inside the DO group never execute.  But SAS sees them as it digests the program, forcing the software to set up storage space to hold the variables that are mentioned, in the order that the SET statements refer to them.

 

 

Regular Contributor
Posts: 212

Re: Proc Transpose Help - Order of transposed columns

Posted in reply to Astounding
So is this physically rebuilding the FinalData dataset by creating the variables in the order of the 4 Set statements?
Super User
Posts: 5,518

Re: Proc Transpose Help - Order of transposed columns

Posted in reply to buechler66

Technically, it's building WANT since that is the name of the output data set.  But that's the right idea.

 

Every DATA step takes place in two phases.  During the compilation phase, the software does the preliminary work.  That includes checking for syntax errors, defining the variables ... as part of that process it sets up the order of the variables.  (If you want to study up on this more, look for PDV or Program Data Vector.)  Only later, during the second phase of the DATA step, do the statements actually execute.  That's when SAS determines that 0 is false and so it doesn't actually execute those SET statements.

 

Regular Contributor
Posts: 212

Re: Proc Transpose Help - Order of transposed columns

Posted in reply to Astounding
This is a great tip. Thanks so much for the help.
Super User
Super User
Posts: 7,079

Re: Proc Transpose Help - Order of transposed columns

[ Edited ]
Posted in reply to buechler66

One method is to create a dummy set of records that has all of the dates in the order that you want so that PROC TRANSPOSE will see those dates first. Right now it is seeing them in the order that they appear after the data has first been sorted by RULE_NM and RULE_ORDER.  You can then drop that resulting row from the output dataset.

 

proc sort data=FinalData (keep=ad_dt_char) out=dates nodupkey;
  by ad_dt_char;
run;

data both ;
  set dates finalData ;
run;

proc transpose data=both out=Want(drop=_name_ where=(rule_nm ne ' '));
  by rule_nm rule_order;
  id ad_dt_char;
  var n;
run;

The other problem with getting the columns in order is that (at least from the name) you have a CHARACTER variable with date values in MM_DD_YYYY (or is it DD_MM_YYYY) format.  That will not sort properly.  You could either change the values to by in YYYY_MM_DD format or use the actual dates and apply a format.

 

If you had another variable with the actual date you could just include it in the first step in my code above.

proc sort data=FinalData (keep=actual_date ad_dt_char) 
   out=dates nodupkey
;
  by actual_date;
run;
Regular Contributor
Posts: 212

Re: Proc Transpose Help - Order of transposed columns

How does setting Dates and FinalData together work exactly? Aren't the two datasets just appended to each other? I guess I'm not seeing how that works, but it appears to have the proper affect.
Solution
‎04-12-2016 05:15 PM
Super User
Super User
Posts: 7,079

Re: Proc Transpose Help - Order of transposed columns

Posted in reply to buechler66

Proc transpose creates the columns as it sees new values of the ID variable. So by putting at least one example of every possible date in the first BY group (and in the right order) then it creates the columns in the right order.  Then the WHERE= dataset option deletes the extra group from the output.  Consider a  simple example.

 

data have ;
   input gender $ year count @@;
cards;
F 2003 10 F 2005 11 M 2002 12 M 2003 13
;;;;
proc transpose data=have out=want ;
  by gender ;
  id year ;
  var count;
run;

This will result in columns GENDER _2003 _2005 _2002.

But if I add an extra set of records that before the F group that has YEAR in the order 2002,2003,2005 then the columns in the output dataset will be created in that order.

Regular Contributor
Posts: 212

Re: Proc Transpose Help - Order of transposed columns

Thanks so much for the help. I appreciate your time.
☑ This topic is solved.

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

Discussion stats
  • 13 replies
  • 754 views
  • 2 likes
  • 4 in conversation