Creating a dataset to visualize a table with startdate and enddate

[roccoz2]

Hi Guys,

Within SAS I have this table:

USER_ID	START_DATE	END_DATE	AMOUNT
12	01Jan2018 0:00:00		2430
18	01Jan2018 0:00:00	13Apr2018 14:21:05	20721
18	13Apr2018 14:21:06	01May2018 16:58:55	21021
18	01May2018 16:58:56	21Jun2018 16:48:40	21521
18	21Jun2018 16:48:41	21Jun2018 16:52:23	21521
18	21Jun2018 16:52:24		21521
22	01Jan2018 0:00:00		1596
26	01Jan2018 0:00:00		1520
28	01Jan2018 0:00:00		4765
31	01Jan2018 0:00:00	10May2018 12:31:51	4412
31	10May2018 12:31:52		4412

A user has a amount that can change over time. I would like to show the sum of amount into a linechart(Visual Analytics).

The sum of amount changes overtime. I would like to show this from startdate 01jan2018 until 31dec2018 (per week).

What is the best way to transform this dataset so this is possible?

I thought something like this, but how?:

	week1	week2	week3	week4	week5	etc
amount

Thanks guys!

Greeting,

Rocco

[Kurt_Bremser · Accepted solution]

Something like

data have;
infile cards dlm=',' dsd;
input USER_ID $ START_DATE :datetime18. END_DATE :datetime18. AMOUNT;
format start_date end_date datetime18.;
cards;
12,01Jan2018 0:00:00,,2430
18,01Jan2018 0:00:00,13Apr2018 14:21:05,20721
18,13Apr2018 14:21:06,01May2018 16:58:55,21021
18,01May2018 16:58:56,21Jun2018 16:48:40,21521
18,21Jun2018 16:48:41,21Jun2018 16:52:23,21521
18,21Jun2018 16:52:24,,21521
22,01Jan2018 0:00:00,,1596
26,01Jan2018 0:00:00,,1520
28,01Jan2018 0:00:00,,4765
31,01Jan2018 0:00:00,10May2018 12:31:51,4412
31,10May2018 12:31:52,,4412
;
run;

data want (keep=user_id week amount);
set have;
if end_date = . then end_date = '31dec2018:23:59:59'dt;
week = week(datepart(start_date));
do while (week < week(datepart(end_date)));
  output;
  week + 1;
end;
run;

proc transpose data=want out=transposed prefix=week;
var amount;
id week;
by user_id;
run;

[roccoz2]

Thanks KurtBremser, it works!