Hello!
I have a dataset that contains multiple rows for individuals. Each individual can appear on multiple rows depending the day of the event of purchase for product.
Data Have;
Ind_ID$ Product$ Day$
1 DVD 2018-02-11
1 DVD 2018-02-17
1 TV 2018-03-01
2 DVD 2018-02-15
2 Car 2018-03-16
2 Bike 2018-05-15
I want to have a dataset where I can count the number of purchase by counting the days and then sum it for each individual and mounth.
Data want;
Ind_ID$ Num_Februari$ Num_March$ Num_april$ Num_May$
1 2 1 .
2 1 1 1
Data Have;
input
Ind_ID$ Product$ Day : yymmdd10. ;
cards;
1 DVD 2018-02-11
1 DVD 2018-02-17
1 TV 2018-03-01
2 DVD 2018-02-15
2 Car 2018-03-16
2 Bike 2018-05-15
;
proc report data=have;
columns ind_id day;
define ind_id/group;
define day/across format=monyy.;
run;
By the way, for your future reference, we usually want working DATA step code when you provide us input data. Adding data have; at the top of your data does not produce working code. I have modified your input to create working DATA step code, but not everyone will do this, and you will get faster and more correct responses when you provide working data step code.
Is it possible to this if I want to put my 'want' in a new table?
The output from PROC REPORT is a table. Do you mean you want it in a SAS data set?
@Chris_LK_87 wrote:
yes, I mean SAS data set
Let me strongly urge you to NOT create a wide SAS data set with column names like JAN20 FEB20 etc. This complicates your programming immensely compared to using a long data set. See Maxim 19. So, here is code to get a long data set with the counts.
proc summary data=have nway;
class ind_id day;
var day;
output out=want(drop=_type_) n=;
run;
This is the basic solution that creates a usable dataset:
proc sql;
create table want as
select
ind_id,
intnx('month',day,0,'b') as month format=yymmd7.,
count(ind_id) as count
from have
group by ind_id, calculated month
;
quit;
First question: are your dates SAS date values?
Second: do you want a data set for further analysis or a report?
Third: why do you show $ at the end of variable names?
A report that shows a count of records, i.e. purchases, from the data you show, if the dates are actual SAS dates could look like:
Proc tabulate data=have; class ind_id day; format day yymon7; table ind_id, (day All='Total') * n=' ' ; run;
Which will show the count per month across the report and a total column.
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.