DATA Step, Macro, Functions and more

Creating a dataset to visualize a table with startdate and enddate

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Creating a dataset to visualize a table with startdate and enddate

Hi Guys,

 

Within SAS I have this table:

USER_IDSTART_DATEEND_DATEAMOUNT
1201Jan2018 0:00:00 2430
1801Jan2018 0:00:0013Apr2018 14:21:0520721
1813Apr2018 14:21:0601May2018 16:58:5521021
1801May2018 16:58:5621Jun2018 16:48:4021521
1821Jun2018 16:48:4121Jun2018 16:52:2321521
1821Jun2018 16:52:24 21521
2201Jan2018 0:00:00 1596
2601Jan2018 0:00:00 1520
2801Jan2018 0:00:00 4765
3101Jan2018 0:00:0010May2018 12:31:514412
3110May2018 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?:

 week1week2week3week4week5etc
amount      

 

Thanks guys!

 

Greeting,

Rocco

 


Accepted Solutions
Solution
3 weeks ago
Super User
Posts: 10,271

Re: Creating a dataset to visualize a table with startdate and enddate

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Solution
3 weeks ago
Super User
Posts: 10,271

Re: Creating a dataset to visualize a table with startdate and enddate

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
New Contributor
Posts: 2

Re: Creating a dataset to visualize a table with startdate and enddate

Posted in reply to KurtBremser

Thanks KurtBremser, it works!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 57 views
  • 1 like
  • 2 in conversation