Help using Base SAS procedures

proc expand deletes data ?

Super Contributor
Posts: 409

proc expand deletes data ?

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

Frequent Contributor
Posts: 110

proc expand deletes data ?

Posts: 8,163

proc expand deletes data ?

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.

Super Contributor
Posts: 409

proc expand deletes data ?

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


Posts: 8,163

Re: proc expand deletes data ?

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

Super Contributor
Posts: 409

proc expand deletes data ?

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

SAS Employee
Posts: 417

proc expand deletes data ?

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.



data have;

input Store $  Date Price Product $;

informat date yymmn6.;

format date date10.;


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


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;


Ask a Question
Discussion stats
  • 6 replies
  • 4 in conversation