Hi, If anyone knowledgeable in proc expand, plese explain to me.. I have prices by month/year (time-series) of about 500 stores and 5 products... The prices for some stores are missing. So I used proc expand to interpolate and fill in the holes.. after running the proc expand a small number of stores dissapeared.. and now left with 490 stores.. why is this happening? thanks
use the options as explained here.
http://support.sas.com/rnd/app/examples/ets/missval/index.htm
It would help if you provided a small (not the full) dataset and the code you submit that ends up deleting some of your stores.
proc expand data=MS.TC_CMA out=MS.TC_CMA_expand to=MONTH;
by Store Product Store_CD City Province;
convert Price counts Index YR_Opt Value DOM / method=JOIN;
id Date; format Date YYMMP7.;
run;
you didn't include any example data that displays the problem
The problem is that I have 500 stores and 5 products, so a combo of 2500 time-series models.. but when I try to fill in the gaps using the above 'expand' code, I end up with 490 stores and 5 products..
something like this:
Store Date Price Product
A 2000.01 234 A1
A 2000.02 246 A1
A 2000.03 258 A1
B 2000.01 564 B2
B 2000.02 534 B2
B 2000.03 533 B2
B 2000.01 345 B1
B 2000.02 354 B1
C 2005.03 356 C2
C 2005.04 367 C2
C 2005.05 359 C2
.. and so on.....(there are alos many other variables attached, just didn't specify them down)..
Hello -
The example code and data you have shared seem not to illustrate the problem you were reporting.
Here is my interpretation of your data and code - everything seems to work as expected.
Thanks,
Udo
data have;
input Store $ Date Price Product $;
informat date yymmn6.;
format date date10.;
datalines;
A 200001 234 A1
A 200002 246 A1
A 200003 258 A1
B 200001 564 B2
B 200002 534 B2
B 200003 533 B2
B 200001 345 B1
B 200002 354 B1
C 200503 356 C2
C 200504 367 C2
C 200505 359 C2
run;
proc sort data=have;by store product;run;
proc expand data=have out=want to=MONTH;
by Store product;
convert Price / method=JOIN;
id Date;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.