BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AllSoEasy
Obsidian | Level 7

I have a data set with dates, and a product category, and I have a number of rows in the data set which have dates in the future, and so the product column is blank for those, but i'd like to essentially replicate the product data throughout those rows. i.e. sample of my data looks like this now:

12/31/2012product1
12/31/2012product2
12/31/2012product3
12/31/2012product4
12/31/2012product5
12/31/2012product6
12/31/2012product7
12/31/2012product8
12/31/2012product9
12/31/2012product10
12/31/2012product11
12/31/2012product12
01/31/2013
01/31/2013
01/31/2013
01/31/2013
01/31/2013
01/31/2013
01/31/2013
01/31/2013
01/31/2013
01/31/2013
01/31/2013
01/31/2013
02/28/2013
02/28/2013
02/28/2013
02/28/2013
02/28/2013
02/28/2013
02/28/2013
02/28/2013
02/28/2013
02/28/2013
02/28/2013
02/28/2013

And I would like it to look like this:

12/31/2012product1
12/31/2012product2
12/31/2012product3
12/31/2012product4
12/31/2012product5
12/31/2012product6
12/31/2012product7
12/31/2012product8
12/31/2012product9
12/31/2012product10
12/31/2012product11
12/31/2012product12
01/31/2013product1
01/31/2013product2
01/31/2013product3
01/31/2013product4
01/31/2013product5
01/31/2013product6
01/31/2013product7
01/31/2013product8
01/31/2013product9
01/31/2013product10
01/31/2013product11
01/31/2013product12
02/28/2013product1
02/28/2013product2
02/28/2013product3
02/28/2013product4
02/28/2013product5
02/28/2013product6
02/28/2013product7
02/28/2013product8
02/28/2013product9
02/28/2013product10
02/28/2013product11
02/28/2013product12

etc.

I thought I could use the lag statement, in a way such as this:

data want;

set have;

if missing(prodcat) then prodcat = lag12(prodcat);

run;

Since the product categories repeat every 12 observations, but this data step has absolutely no effect on the data set, if i replace the lag function with just a constant string, I see that string populated throughout the rows -- so why doesn't the lag function put the data in properly?

Thanks!

-Ryan

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

Yes, that's correct.  Very tricky ... LAG processes the ORIGINAL value in the "stack", not the final changed value.  Here's another approach:

proc sql noprint;

   create table dates as select distinct date from have;

   create table products as select distinct product from have where product > ' ';

   create table want as select * from dates, products order by date, product;

quit;

Good luck.

View solution in original post

6 REPLIES 6
Tom
Super User Tom
Super User

You normally do NOT want to reference LAG() conditionally.  It does not "look back", instead it is popping values off a stack generated by the previous EXECUTIONS of the LAG() function.

lag12=lag12(prodcat);

if missing(prodcat) then prodcat = lag12;

AllSoEasy
Obsidian | Level 7

Thanks so much! However it only partially worked...With your code modification it now populates the following 12 rows (01/31/2013) months, but none after that. I tried a retain statement with no luck...Any suggestions?

Astounding
PROC Star

Yes, that's correct.  Very tricky ... LAG processes the ORIGINAL value in the "stack", not the final changed value.  Here's another approach:

proc sql noprint;

   create table dates as select distinct date from have;

   create table products as select distinct product from have where product > ' ';

   create table want as select * from dates, products order by date, product;

quit;

Good luck.

AllSoEasy
Obsidian | Level 7

Thank you so much Astounding! That works perfectly and seems to be a very elegant solution.

Many thanks!!

Tom
Super User Tom
Super User

It sounds like your actual problem is not related to LAG() at all.

Do you want to assign all possible productions to all possible dates? then the SQL solution proposed looks good.

If you really did want to "cycle" the non-missing values you might be able to use POINT=.

data products;

  set have;

  where not missing(product);

  keep product;

run;

data want;

  set have ;

  if product = ' ' then do ;

    p+1;

    if p > nobs then p=1;

    set products point=p nobs=nobs;

end;

run;

Reeza
Super User

You could always try a cross join instead:

proc sql;

create table want as

select * from (

select distinct date from have

cross join

select distinct product from have)

order by date, product;

quit;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 938 views
  • 3 likes
  • 4 in conversation