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

Hi....I am trying to get the output to order the columns that have been transposed. It seems the problem that I am having is that because not all ID's have data for each YrMonth, and since the data is sorted by ID and then YrMonth, the output and columns begin withthe results from the first ID. Any suggestions how I can remedy this so that the first transposed column is the first ordered YrMonth....Thanks.

 

PROC SORT DATA=NEW1;
BY NAME ID YRMONTH;
RUN;

PROC TRANSPOSE DATA=NEW1 OUT=WANT;
BY NAME ID;
ID YRMONTH;
VAR COST;
RUN;

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

you can always change the order of variables after proc transpose .

Use SQL select the right order variable name and Use RETAIN.

 

proc sql;

 select distinct cats('_',year) into : list separated by ' ' 

 from have 

   order by year;

quit;

..........

proc transpose out=want;.........

 

data final;

 retain Gender &list Total;

 set want;

run;

View solution in original post

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

 

Please don't code all in uppercase - it is very difficult to read.  Also, providing test data in the form of a datastep will yield you tested code.  So what your saying is you end up with something like:

ID    _201501  _201506  _201504...

Yes?

 

If so then maybe you could create a shell dataset, and then set the transposed data back to that -

data new1;
  name="abc"; id=1; yrmonth=201501; cost=34; output;
  name="abc"; id=1; yrmonth=201505; cost=.; output;
  name="abc"; id=1; yrmonth=201506; cost=54; output;
run;
proc sql; 
  create table TEMP (_201501 num,_201505 num,_201506 num);
run;
proc sort data=new1;
  by name id yrmonth;
run;
proc transpose data=new1 out=want;
  by name id;
  id yrmonth;
  var cost;
run;
data want;
  set temp want;
run;

 

 

buechler66
Barite | Level 11

Forum genius Tom helped me with this yesterday...

 

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.  You can then drop that resulting row from the output dataset.

 

proc sort data=FinalData (keep=ad_dt) out=dates nodupkey;
  by ad_dt;
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;

 

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.

Ksharp
Super User

you can always change the order of variables after proc transpose .

Use SQL select the right order variable name and Use RETAIN.

 

proc sql;

 select distinct cats('_',year) into : list separated by ' ' 

 from have 

   order by year;

quit;

..........

proc transpose out=want;.........

 

data final;

 retain Gender &list Total;

 set want;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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