Help using Base SAS procedures

Filling missing values with a range

Reply
Occasional Contributor
Posts: 17

Filling missing values with a range

Hi

I have a dataset in the format below. I am interested in having a Days to Departure range of 0-365 for all products/subproducts for a journey date.

journey_Dateproduct_codeproduct_subcodeDays_to_DepBooked
01/04/20141001201
01/04/201410013009
01/04/2014101111
01/04/201410113003
Super User
Posts: 10,500

Re: Filling missing values with a range

What should the output look like for your example data?

And since dates are involved, what about leap years?

Occasional Contributor
Posts: 17

Re: Filling missing values with a range

Hi the output should have days to departure (0-365) essentially 365 rows for each product code and sub code ... For product code 100 and sub code 1, the days to departure will be 0 to 365 (365 rows) and value of sold will be recorded against rows 20 and 300...

I always look for last 365 rows, so no problems with leap years for now.

My approach has been to create a  lookup dataset for days to departure (with one column containing 365 rows each representing one day to departure). I then multiply that table with my original dataset to achieve what I said above. However, I was wondering if there is an alternative way (quicker) to do the same?

The newly created dataset will have a missing value for days there was no sale and that's fine

Trusted Advisor
Posts: 1,130

Re: Filling missing values with a range

you mean to format the Days_to_Dep with 0-365

Thanks,
Jag
Occasional Contributor
Posts: 17

Re: Filling missing values with a range

Hi,

I would like the final dataset to have 365 rows for each product code and sub code. So for product 100 and subcode 1, the resulting dataset will have only values for days 20 and 300, all others rows will have a missing value.

I am only interested in 0-365 days, so not worried about leap years.

My approach has been to create a dataset with 365 rows and a CROSS JOIN to the above table to get the desired output, but I was wondering if there are alternative solutions?

Thanks

Ask a Question
Discussion stats
  • 4 replies
  • 268 views
  • 0 likes
  • 3 in conversation