Proc Transpose

[Kalai2008]

I have the data as follows, Months from Jan16  to Dec18. The output for the dates are not in proper order, how to get the months in the proper order, starting from Jan 2016 , feb 2016 , may 2016  till .....dec 2018. My current output dates are not in proper order.

ID    Date                    Status

1     July 31, 2016          Yes

1     July  31 2017           No

1      July 31  2018         No

2     June 30, 2016         Yes

2     August 31, 2018      No

Output Needed:

ID    June 30 2016    June 30 2017     July 31 2016   July 31 2017  July 31 2018  Aug 31 2016 Aug 30 2017 Aug 2019

  1                                                               yes       no                no

Proc transpose data=test out=test1 (drop=_NAME_);

by id;

var status;

id date;run;

[ballardw]

How are you going to use that transposed data set?

It appears that your "dates" are character values and as such, with spaces and commas for instance, will not actually work as SAS variable names.

[Kalai2008]

Date is in Worddate. Numeric format. I need month,date and year. I can try without spaces and commas.

[Reeza]

Why are you doing this? It's usually harder to manage your data in this structure, especially if it changes over time. You can use IDLABEL within PROC TRANSPOSE to control the labels so they can show with the worddate format and then your variable names can be DYYMMDD format, which will then be easier to order in a defined order.

[Kalai2008]

I never used ID Label statement to change dates. How would you do that?

[Reeza]

Your proc transpose code is wrong, I would expect to see DATE be the ID and IDLABEL variable.
and status as the variable. The variable is the data that goes in the cells, ID/IDLABEL are the variable names/column headers.

[Kalai2008]

I corrected my code here, actually i used the ID as Date and Var as Status.

[Reeza]

If you want to do what I suggested, I would add an intermediary step and create a new variable using:

DATE_VARNAME = catt("D", put(date, yymmddd10.));

Use that created variable as the ID and then format the date variable the way you want to show it and specify that as the label.

[Kalai2008]

Thank you, I created the intermediary step and used as ID variable, still the columns are out of order.

Cust ID  D2017-11-30  D2018-01-31  D2018-02-28 D2016-03-31

I need the ID variable Dates to be in order starting from 2016 jan to 2018 Dec.

[Reeza]

Did you sort the data ahead of the transpose?

[Kalai2008]

Yes, I sorted by id, date_varname.

If i do only date_varname, then getting error: 

ERROR: Data set WORK.TEST5 is not sorted in ascending sequence. The current BY group has  id = 1021 and the next BY group
has id = 1051.

[data_null__ · Accepted solution]

---

@Kalai2008 wrote:

I have the data as follows, Months from Jan16  to Dec18. The output for the dates are not in proper order, how to get the months in the proper order, starting from Jan 2016 , feb 2016 , may 2016  till .....dec 2018. My current output dates are not in proper order.

ID    Date                    Status

1     July 31, 2016          Yes

1     July  31 2017           No

1      July 31  2018         No

2     June 30, 2016         Yes

2     August 31, 2018      No

Output Needed:

ID    June 30 2016    June 30 2017     July 31 2016   July 31 2017  July 31 2018  Aug 31 2016 Aug 30 2017 Aug 2019

  1                                                               yes       no                no

Proc transpose data=test out=test1 (drop=_NAME_);

by id;

var status;

id date;run;

---

Looks like you want to order the variable names by YEAR within DAY.  The technique is the same for any ordering you want.  Make a frame that has all the dates(variables) that will be created by transpose and order them by year within day.

data status;
   input ID    Date & anydtdte.  Status $;
   format date yymmdd10.;
   idlabel = put(date,worddate.);
   day = day(date);
   cards;
1 July 31, 2016        Yes
1 July 31 2017         No
1 July 31 2018         No
2 June 30, 2016        Yes
2 August 31, 2018      No
;;;;
   run;

proc print;
   run;
data frame;
   if 0 then set status;
   set status(drop=id) status;
   run;
proc sort nodupkey;
   by id day date;
   run;
Proc transpose data=frame out=test1(drop=_name_) prefix=D;
   by id;
   var status;
   id date;
   idlabel idlabel;
   run;
proc print;
proc contents varnum;
   ods select position;
   run;