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;
@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;
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.
Date is in Worddate. Numeric format. I need month,date and year. I can try without spaces and commas.
I never used ID Label statement to change dates. How would you do that?
I corrected my code here, actually i used the ID as Date and Var as Status.
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.
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.
@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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.