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

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

13 REPLIES 13
Reeza
Super User

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;

 

buechler66
Barite | Level 11

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 ;

Reeza
Super User

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;

Astounding
PROC Star

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.

buechler66
Barite | Level 11
Can you explain the 'if 0' part of the code? I'm not sure what is 0?
Astounding
PROC Star

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.

 

 

buechler66
Barite | Level 11
So is this physically rebuilding the FinalData dataset by creating the variables in the order of the 4 Set statements?
Astounding
PROC Star

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.

 

buechler66
Barite | Level 11
This is a great tip. Thanks so much for the help.
Tom
Super User Tom
Super User

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;
buechler66
Barite | Level 11
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.
Tom
Super User Tom
Super User

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.

buechler66
Barite | Level 11
Thanks so much for the help. I appreciate your time.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 13 replies
  • 4791 views
  • 3 likes
  • 4 in conversation