Should I use transpose or other functions to get the table I want

[CynthiaWei]

Hi SAS Pros,

I am having the dataset :

data Have;
	input id $ datestr :$10. Yes_No;
	format date mmddyy10.;
	date = input(datestr!!"-01",yymmdd10.);
	drop datestr;
	datalines;
1 2015-04-01 1
1 2015-04-12 0
1 2015-05-01 0
1 2016-02-01 1
1 2016-07-01 1
2 2015-01-01 0
2 2015-06-24 1
2 2015-07-02 0
2 2015-11-25 1
2 2015-12-24 0
2 2015-12-31 0
2 2016-01-20 0

;
run;

And, what I want is to create a table that each row represents a month and have all the observations happened in that month, regardless Yes_No=1 or 0, but still have the variable Yes_No in the final dataset. The value of Yes_No for each row is the value for the first observation of that month (i.e. Yes_No=1 for 4/1/2015, Yes_No=0 for 12/24/2015). If there are two or more observations in one month then have them in the same row, just like the table below:

ID Yes_No month dispense_date dispense_date_of_following_1 dispense_date_of_following_2

1 1 2015-04 4/1/2015 4/12/2015

1 0 2015-05 5/1/2015

1 1 2016-02 2/1/2016

1 1 2016-07 7/1/2016

2 0 2015-01 1/1/2015

2 1 2015-06 6/24/2015

2 0 2015-07 7/2/2015

2 1 2015-11 11/25/2015

2 0 2015-12 12/24/2015 12/31/2015

2 0 2016-01 1/20/2016

Thank you very much for any help!!!

Best regards,

C

[andreas_lds · Accepted solution]

Transpose works, but the table you want looks more like a report than a dataset, so i thought about using proc report with across, but failed to get it right. So here's proc transpose:

data extended;
   set have;
   
   format month yymms7.;
   
   month = date;
run;

proc transpose data=extended out=transposed(drop= _name_ where=(not missing(dispense_date_1))) prefix=dispense_date_;
   by id month;
   var date;
   copy yes_no;
run;