topic Re: How do I group dates in an Excel pivot table which derives from a SAS table? in New SAS User

How do I group dates in an Excel pivot table which derives from a SAS table?

ashlyn — Thu, 02 Jan 2020 09:53:06 GMT

I used the SAS add-in tool to create a pivot table in Excel. However, I am unable to group dates together by months/years as the pivot recognises the dates as numeric values. The SAS table is from the SAS server and the date column is in a MMDDYY10. format.

Re: How do I group dates in an Excel pivot table which derives from a SAS table?

Kurt_Bremser — Thu, 02 Jan 2020 11:48:07 GMT

You have SAS. Do the pivot/transpose there.

Re: How do I group dates in an Excel pivot table which derives from a SAS table?

ashlyn — Mon, 06 Jan 2020 03:25:00 GMT

Hi KurtBremser, thanks for your input. However, I would like to remind you that there are many SAS users here of varying proficiency (I am very new to SAS), you do not have to be sarcastic. Hopefully everyone can create a healthy and welcoming environment here to learn SAS.

Re: How do I group dates in an Excel pivot table which derives from a SAS table?

Kurt_Bremser — Mon, 06 Jan 2020 08:35:13 GMT

Please give us an example of your source data in usable form (data step with datalines), and what you expect out of it, so that we can show you how to do it.

Re: How do I group dates in an Excel pivot table which derives from a SAS table?

ashlyn — Tue, 07 Jan 2020 07:24:11 GMT

I have a table which is updated on a weekly basis by the admin in the SAS server. I would like to create a pivot table in Excel which will be refreshed weekly when the table is updated. As I do not know how to pivot/transpose the data using SAS codes, I tried using the SAS add-in tool in Microsoft Excel to create the pivot table which gives me an issue in grouping dates in the pivot table.

Re: How do I group dates in an Excel pivot table which derives from a SAS table?

Kurt_Bremser — Tue, 07 Jan 2020 07:39:23 GMT

Please read my post again:

Please give us an example of your source data in usable form (data step with datalines), and what you expect out of it, so that we can show you how to do it.

The intended outcome can be a screenshot, a listing, whatever. Burt please supply example data in a form that allows us to recreate it with a simple copy/paste and submit of the code.

A mere repetition of your original problem description is not something we can work with.

Re: How do I group dates in an Excel pivot table which derives from a SAS table?

ashlyn — Tue, 07 Jan 2020 07:58:22 GMT

Date     Product ID Product
1/1/19   12345678  A
1/1/19   18656168  B 
2/1/19   13841388  A
2/1/19   18658779  B
15/1/19 16518464  A
18/1/19 18746351  B
10/2/19 18763156  A
19/3/19 18974631  A
25/3/19 19873210  A

I'm hoping to get a pivot table with the dates group by months and product, and the count.

Jan 2019 Product A 3

Jan 2019 Product B 3

Feb 2019 Product A 1

Feb 2019 Product B 0

Mar 2019 Product A 2

Mar 2019 Product B 0

Re: How do I group dates in an Excel pivot table which derives from a SAS table?

Kurt_Bremser — Tue, 07 Jan 2020 08:30:07 GMT

That's not really a pivot, that's just a summary on months and ID's.

Start by creating a month column in the original data:

data have;
input Date :ddmmyy10. Product $ ID $;
format date yymmddd10.;
month = put(date,yymmd7.);
format date yymmddd10.;
datalines;
1/1/19 12345678 A
1/1/19 18656168 B 
2/1/19 13841388 A
2/1/19 18658779 B
15/1/19 16518464 A
18/1/19 18746351 B
10/2/19 18763156 A
19/3/19 18974631 A
25/3/19 19873210 A
;

(note the data step with datalines to present data)

Then, in SQL, create a lookup table containing all possible month/ID combinations, and then use that in a second step to create the wanted table:

proc sql;
create table months as select distinct a.month, b.id
from have a, have b;
create table want as
select
  a.month, a.id, count(b.product) as count
from months a left join have b
on a.month = b.month and a.id = b.id
group by a.month, a.id;
quit;

Output that:

proc print data=want noobs;
run;

Result:

 month     ID    count

2019-01    A       3  
2019-01    B       3  
2019-02    A       1  
2019-02    B       0  
2019-03    A       2  
2019-03    B       0

Without the lookup table, we would not get those lines with a zero.

Re: How do I group dates in an Excel pivot table which derives from a SAS table?

ashlyn — Tue, 07 Jan 2020 08:41:17 GMT

Thanks for the solution. However, I literally need a pivot table in Microsoft Excel that is linked to the SAS table. This Excel file will be shared across multiple parties and having the pivot table can allow parties to get the different variables they need (which are not shown in the example above). The variables I mentioned above will form the main pivot table that the users can see when they open the Excel file.

The SAS Add-in tool solved my initial problem in creating the pivot table in Excel but I am just stuck at grouping dates into months. Hope my question is clear.

Re: How do I group dates in an Excel pivot table which derives from a SAS table?

Kurt_Bremser — Tue, 07 Jan 2020 09:06:11 GMT

My post shows how to create a month variable from the dates. Use that in Excel.

month = put(date,yymmd7.);