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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.