CODEWITHSUNDEEP
sassummary
Helen
Helen

Reputation: 607

Special type of summary in SAS

I have the following data:

enter image description here

I would like to know how many unique clients that each month of each year has a certain product. From and To columns are when the product is "valid", and a customer get a new row if he changes his size, colour or product, or a year has passed. I do not care about colour or size, only the type of product. There are ways to do this I know, but they are very tedious.

For example, for month 1 in year 2014, I would like to know how many unique clients that have product 1, product 2, or product 3. 

Year    Month   no product 1    no product 2    no product 3
2014    1           1             0                 0

(Only Gerald was "valid" at that time, and he had only product 1)

I would like to have a list like this for all "valid" years and months.

EDIT: data:

name    from          to        colour  size    product
Jenny   15JAN2015   15JAN2016'  red     small   1
Jenny   15JAN2016'  15JAN2017'  green   big     1
Jenny   15JAN2017'  15JAN2018'  blue    big     3
Bob     05APR2014   05APR2015   blue    small   2
Bob     05APR2015   05APR2016   green   small   2
Gerald  23MAY2013   23DEC2013   red     small   2
Gerald  23DEC2013   23MAY2014   yellow  big     1
Gerald  23MAY2014   04SEP2014   green   big     1
Gerald  04SEP2014   25DEC2014   red     small   2
Hope    23MAY2014   04SEP2014   red     small   1
Hope    04SEP2014   25DEC2014   red     small   1
Siri    15JAN2016'  15JAN2017'  red     small   1

Upvotes: 0

Views: 36

Answers (1)

Longfish
Longfish

Reputation: 7602

If you expand the original data so that you have a row for every month a customer holds a product for, then it is a simple matter of doing a frequency count and transposing the result to get the format desired. The only difference in my answer is that I have shown the year and month as 1 column as it makes the loop much easier.

/* source data */
data have;
input name $ from_dt :date9. to_dt :date9. colour $ size $ product;
format from_dt to_dt date9.;
datalines;
Jenny 15JAN2015 15JAN2016 red small 1 
Jenny 15JAN2016' 15JAN2017' green big 1 
Jenny 15JAN2017' 15JAN2018' blue big 3 
Bob 05APR2014 05APR2015 blue small 2 
Bob 05APR2015 05APR2016 green small 2 
Gerald 23MAY2013 23DEC2013 red small 2 
Gerald 23DEC2013 23MAY2014 yellow big 1 
Gerald 23MAY2014 04SEP2014 green big 1 
Gerald 04SEP2014 25DEC2014 red small 2 
Hope 23MAY2014 04SEP2014 red small 1 
Hope 04SEP2014 25DEC2014 red small 1 
Siri 15JAN2016' 15JAN2017' red small 1
;
run;

/* expand data to have a row for every month */
data temp1;
format mthyr yymm8.;
set have;
do i = 0 to intck('month',intnx('month',from_dt,0),intnx('month',to_dt,0));
mthyr = intnx('month',from_dt,i);
output;
end;
run;

/* count frequencies of products per month */
proc freq data=temp1 noprint;
table mthyr*product / sparse out=temp2;
run;

/* transpose data */
proc transpose data=temp2 out=want (drop=_:) prefix=product;
by mthyr;
id product;
var count;
run;

Upvotes: 1

Related Questions