topic proc expand deletes data ? in SAS Procedures

proc expand deletes data ?

podarum — Thu, 22 Mar 2012 19:36:06 GMT

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

proc expand deletes data ?

sassharp — Thu, 22 Mar 2012 19:40:25 GMT

use the options as explained here.

http://support.sas.com/rnd/app/examples/ets/missval/index.htm

proc expand deletes data ?

art297 — Thu, 22 Mar 2012 20:00:00 GMT

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 deletes data ?

podarum — Thu, 22 Mar 2012 20:04:03 GMT

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;

Re: proc expand deletes data ?

art297 — Thu, 22 Mar 2012 21:14:39 GMT

you didn't include any example data that displays the problem

proc expand deletes data ?

podarum — Fri, 23 Mar 2012 12:40:36 GMT

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)..

proc expand deletes data ?

udo_sas — Fri, 23 Mar 2012 13:38:19 GMT

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;