topic Re: Proc Transpose in SAS Programming

Proc Transpose

Kalai2008 — Wed, 27 Mar 2019 20:05:55 GMT

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;

Re: Proc Transpose

ballardw — Wed, 27 Mar 2019 19:24:38 GMT

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.

Re: Proc Transpose

Kalai2008 — Wed, 27 Mar 2019 19:29:49 GMT

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

Re: Proc Transpose

Reeza — Wed, 27 Mar 2019 19:40:39 GMT

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.

Re: Proc Transpose

Kalai2008 — Wed, 27 Mar 2019 20:00:36 GMT

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

Re: Proc Transpose

Reeza — Wed, 27 Mar 2019 20:04:00 GMT

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.

Re: Proc Transpose

Kalai2008 — Wed, 27 Mar 2019 20:07:10 GMT

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

Re: Proc Transpose

Reeza — Wed, 27 Mar 2019 20:13:29 GMT

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.

Re: Proc Transpose

data_null__ — Wed, 27 Mar 2019 20:49:58 GMT

@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;

Re: Proc Transpose

Kalai2008 — Wed, 27 Mar 2019 20:56:46 GMT

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.

Re: Proc Transpose

Reeza — Wed, 27 Mar 2019 21:00:39 GMT

Did you sort the data ahead of the transpose?

Re: Proc Transpose

Kalai2008 — Wed, 27 Mar 2019 21:44:01 GMT

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.