topic Re: Collapsing and Reshaping in SAS Programming

Collapsing and Reshaping

newsas007 — Mon, 25 Jan 2021 18:51:14 GMT

Hi All,

Here is the data i have been working on. I tried to use transpose and reshape and it did not work. I would like to collapse the ADATE variable into pre and post and have their corresponding Prediff and PostDiff variables.

Data Have:

ID	Name	ReportDate	Match	ADATE	Generic	Prediff	Postdiff
1	TP	9/27/2016	A,B
3	TE	10/26/2016
3	TP	10/26/2016	A,B	11/25/2016	A		29.10
4	TP	11/18/2016	A,B	8/16/2015	A	-460.797
4	TP	11/18/2016	A,B	1/14/2017	A		56.20
7	TP	2/18/2017	A,B	3/10/2017	A		19.38
8	TP	2/21/2017	A,B	3/7/2017	A		13.31
8	TP	2/21/2017	A,B	2/21/2017	A	-0.68
9	TP	4/17/2017	A,B	4/24/2017	A		6.136
9	TP	4/17/2017	A,B	4/15/2017	A	-2.86

Data Want:

ID	Name	ReportDate	Match	ADATE_pre	ADATE_Post	Generic	Prediff	Postdiff
1	TP	9/27/2016	A,B
3	TE	10/26/2016
3	TP	10/26/2016	A,B	11/25/2016		A		29.10
4	TP	11/18/2016	A,B	8/16/2015	1/14/2017	A	-460.79	56.2
7	TP	2/18/2017	A,B	3/10/2017		A		19.38
8	TP	2/21/2017	A,B	3/7/2017	2/21/2017	A	-0.68	13.3
9	TP	4/17/2017	A,B	4/15/2017	4/24/2017	A	-2.86	6.136

Re: Collapsing and Reshaping

ballardw — Mon, 25 Jan 2021 19:03:18 GMT

Are there ever more than 2 Adate values for a single ID / Report date combination?

Does Name actually play a role in this?You show two different name values for Id=3.

Will MATCH be exactly the same all the values to collapse? If not you need to provide some more rules/ examples.

Are your date variables actual SAS numeric values with a date format attached or character?

Re: Collapsing and Reshaping

newsas007 — Mon, 25 Jan 2021 19:15:30 GMT

No there are no more than 2 ADate Values for Single ID/Report Date.
There are several Name categories but the name shouldn't play a role in this.
Match will be for the values and will not change.
The dates are in SAS numeric values

Re: Collapsing and Reshaping

PeterClemmensen — Mon, 25 Jan 2021 19:24:54 GMT

I think this captures all the logic.

data have;
input ID Name $ ReportDate :mmddyy10. Match $ ADATE :mmddyy10. Generic $ Prediff Postdiff;
infile datalines dlm = '|';
format ReportDate ADATE mmddyy10.;
datalines;
1|TP|9/27/2016 |A,B|          | |        |
3|TE|10/26/2016|   |          | |        |
3|TP|10/26/2016|A,B|11/25/2016|A|        |29.10
4|TP|11/18/2016|A,B|8/16/2015 |A|-460.797| 
4|TP|11/18/2016|A,B|1/14/2017 |A|        |56.20
7|TP|2/18/2017 |A,B|3/10/2017 |A|        |19.38
8|TP|2/21/2017 |A,B|3/7/2017  |A|        |13.31
8|TP|2/21/2017 |A,B|2/21/2017 |A|-0.68   |
9|TP|4/17/2017 |A,B|4/24/2017 |A|        |6.136
9|TP|4/17/2017 |A,B|4/15/2017 |A|-2.86   |
;

data want(drop = pre post ADATE);
   format ID Name ReportDate Match ADATE_pre ADATE_Post Generic Prediff Postdiff;
   do until (last.Name);
      set have;
      by ID Name;
      if last.Name                   then ADATE_pre  = ADATE;
      if first.Name & not last.Name  then ADATE_post = ADATE;
      post = max(post, Postdiff);
      pre = max(pre, Prediff);
   end;
   Prediff = pre;
   Postdiff = post;
   format ADATE_pre ADATE_post mmddyy10.;
run;

Result:

ID  Name  ReportDate  Match  ADATE_pre   ADATE_Post    Generic  Prediff   Postdiff 
1   TP    09/27/2016  A,B    .           .                      .         . 
3   TE    10/26/2016         .           .                      .         . 
3   TP    10/26/2016  A,B    11/25/2016  .             A        .         29.100 
4   TP    11/18/2016  A,B    01/14/2017  08/16/2015    A        -460.797  56.200 
7   TP    02/18/2017  A,B    03/10/2017  .             A        .         19.380 
8   TP    02/21/2017  A,B    02/21/2017  03/07/2017    A        -0.680    13.310 
9   TP    04/17/2017  A,B    04/15/2017  04/24/2017    A        -2.860    6.136

Re: Collapsing and Reshaping

ballardw — Mon, 25 Jan 2021 20:11:55 GMT

@newsas007 wrote:
No there are no more than 2 ADate Values for Single ID/Report Date.
There are several Name categories but the name shouldn't play a role in this.
Match will be for the values and will not change.
The dates are in SAS numeric values

Then you need to provide examples with more Adate values and what the result would be. Very likely you will also have to provide one or more rules about why which date becomes the Pre and Post date and how the Pre and Post Diff values are determined.