Count days within months per ID

[Chris_LK_87]

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

[PaigeMiller]

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.

[Chris_LK_87]

Thank you for your answer!

Is it possible to this if I want to put my 'want' in a new table?

Thanks for reminding me to post the full code.

[PaigeMiller]

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]

It seems to work in the results (HTML) table but not in the new table want.

In the results table i get correct columns like ; APR18, MAR18.

In the table want i get columns like; _C2_,_C3_

[Chris_LK_87]

yes, I mean SAS data set

[PaigeMiller]

---

@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;

[Kurt_Bremser]

• Dollar signs are not valid in SAS variable names
• Why do you want a mostly unusable wide dataset?

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;

[ballardw]

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.

[Chris_LK_87]

First question: are your dates SAS date values?
Yes.

Second: do you want a data set for further analysis or a report?
I want a dataset

Third: why do you show $ at the end of variable names?
It was a mistake, sorry.