topic Re: Table rearrangement in New SAS User

Table rearrangement

cactooos — Mon, 26 Oct 2020 11:06:27 GMT

Hi, Could you please help me to rearrange the build of this table? It can have from 0 rows to anything but hardly this will be the case of more than 20. I am already using macro variable &noobs_have elsewhere. I want the table to be like on the pic below

data have; input day : $9. Date : $16. delta; cards; 22OCT2020 22OCT20:15:19:10 0 22OCT2020 22OCT20:15:25:20 370 23OCT2020 23OCT20:00:19:54 2 23OCT2020 23OCT20:00:20:02 8 ; run; data _NULL_; if 0 then set have nobs=n; call symputx('noobs_have',n); stop; run;

Thank you in advance for any help

Cheers

Re: Table rearrangement

PaigeMiller — Mon, 26 Oct 2020 11:19:41 GMT

Transposing the data is a way to do this, and then you don't need to know how many rows there are, because PROC TRANSPOSE takes care of that for you. This solution assumes there are never more than two rows on a given day.

data have;
  input day :date9. Date :datetime16. delta;
  format day date9. date datetime16.;
  cards;
22OCT2020 22OCT20:15:19:10 0
22OCT2020 22OCT20:15:25:20 370
23OCT2020 23OCT20:00:19:54 2
23OCT2020 23OCT20:00:20:02 8
  ;
run;
proc transpose data=have out=have_t;
	by day;
	var date;
run;
data want;
	set have_t;
	delta=col2-col1;
run;

Re: Table rearrangement

Ksharp — Mon, 26 Oct 2020 11:25:08 GMT

data have;
  input day : $9. Date : $16. delta;
  cards;
22OCT2020 22OCT20:15:19:10 0
22OCT2020 22OCT20:15:25:20 370
23OCT2020 23OCT20:00:19:54 2
23OCT2020 23OCT20:00:20:02 8
  ;
run;

data want;
 do until(last.day);
  set have;
  by day;
  if first.day then from=date;
 end;
 to=date;
 keep day from to delta;
 run;

Re: Table rearrangement

cactooos — Mon, 26 Oct 2020 11:26:32 GMT

Thank you for that, but that is the point that it can be any even number of rows in this table and all of them could be for one day as well.

Re: Table rearrangement

PaigeMiller — Mon, 26 Oct 2020 11:29:33 GMT

@cactooos wrote:
Thank you for that, but that is the point that it can be any even number of rows in this table and all of them could be for one day as well.

This was not mentioned in the original message. If they are always in pairs, then the solution from @Ksharp should work well.

Re: Table rearrangement

cactooos — Mon, 26 Oct 2020 11:35:56 GMT

Thanks, I didn't mention that in the first message. They always comes as a pair but can be any number of pairs per table and per day. So this is also possible:

data have;
  input day : $9. Date : $16. delta;
  cards;
22OCT2020 22OCT20:15:19:10 0
22OCT2020 22OCT20:15:25:20 370
22OCT2020 22OCT20:16:19:10 0
22OCT2020 22OCT20:16:25:30 380
22OCT2020 22OCT20:17:19:10 0
22OCT2020 22OCT20:17:25:40 390
24OCT2020 24OCT20:00:19:54 2
24OCT2020 24OCT20:00:20:02 8
  ;
run;

Re: Table rearrangement

PaigeMiller — Mon, 26 Oct 2020 12:33:43 GMT

Another solution is to assign an ID number to the pairs, and then use PROC TRANSPOSE as I showed with BY ID.

data have;
  input day :date9. Date :datetime16. delta;
  id = floor((_n_-1)/2);
  format day date9. date datetime16.;
  cards;
...
  ;