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
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;
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;
Thanks KurtBremser, it works!
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: