Help using Base SAS procedures

proc expand deletes data ?

Reply
Super Contributor
Posts: 396

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 ?

PROC Star
Posts: 7,366

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: 396

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

run;

PROC Star
Posts: 7,366

Re: proc expand deletes data ?

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

Super Contributor
Posts: 396

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: 416

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.

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;

Ask a Question
Discussion stats
  • 6 replies
  • 402 views
  • 0 likes
  • 4 in conversation