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;
Comments
Post a Comment