Creating a Correlation Matrix between Types in a Column based on Date Column in SAS -

i have table looks this:

product_type    sales   date               470     1/1/2017               233     1/2/2017               312     1/3/2017               139     1/4/2017               343     1/5/2017               234     1/6/2017 b               441     1/1/2017 b               175     1/2/2017 b               293     1/3/2017 b               109     1/4/2017 b               314     1/5/2017 b                55     1/6/2017 c               292     1/1/2017 c               212     1/2/2017 c               372     1/3/2017 c               452     1/4/2017 c               362     1/5/2017 c                 6     1/6/2017 

i'm trying create correlation matrix gives me correlation product_type based on dates. need output this:

      b   c   1.0 0.8 0.1 b   0.2 1.0 0.2 c   0.6 0.2 1.0 

the way know how creating new table breaking out each product_type column based on date this:

proc sql; create table test select date       ,sum(case when product_type = 'a' sales else . end)       ,sum(case when product_type = 'b' sales else . end) b       ,sum(case when product_type = 'c' sales else . end) c work.data group 1; quit;  proc corr data=work.test; run; 

this gets matrix want, few dozen unique values in product_types time-consuming write line each one.

i've tried this:

proc corr data=data; product_type notsorted; run; 

but gets correlation between date , sales, not i'm looking for.

i tried well:

proc corr data=data; date product_type notsorted; run; 

i don't know code doing, crashed sas.

any thoughts on how efficiently using first table?

you need reformat data each product column , can find correlation between products. use proc transpose change data long wide , proc corr create matrix. can dynamic if add more products/dates still work. otherwise can manually via sql method you've shown above.

edit: here's sample code.

proc sort data=have; date product_type; run;  proc transpose data=have out=wide prefix=pt_; date; var sales; id product_type; idlabel product_type; run;  proc corr data=wide; var pt_:; run;