topic Re: Count days within months per ID in SAS Programming

Count days within months per ID

Chris_LK_87 — Mon, 15 Jun 2020 13:10:30 GMT

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

Re: Count days within months per ID

PaigeMiller — Mon, 15 Jun 2020 13:34:27 GMT

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.

Re: Count days within months per ID

Chris_LK_87 — Mon, 15 Jun 2020 13:32:43 GMT

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.

Re: Count days within months per ID

PaigeMiller — Mon, 15 Jun 2020 13:41:26 GMT

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?

Re: Count days within months per ID

Chris_LK_87 — Mon, 15 Jun 2020 13:53:32 GMT

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_

Re: Count days within months per ID

Chris_LK_87 — Mon, 15 Jun 2020 13:54:59 GMT

yes, I mean SAS data set

Re: Count days within months per ID

Kurt_Bremser — Mon, 15 Jun 2020 14:10:43 GMT

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;

Re: Count days within months per ID

ballardw — Mon, 15 Jun 2020 14:11:50 GMT

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.

Re: Count days within months per ID

PaigeMiller — Mon, 15 Jun 2020 14:21:44 GMT

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

Re: Count days within months per ID

Chris_LK_87 — Mon, 15 Jun 2020 14:38:38 GMT

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.