BookmarkSubscribeRSS Feed
thanikondharish
Calcite | Level 5

data s ;
input id $ month sale ;
cards ;
111 1 486
111 2 486
111 3 486
112 1 487
112 2 489
112 3 789
112 4 963
113 1 789
113 2 879
114 1 589
;

 

actually 111 id hasn't 4th month like that how to find missing months and assign zero value to sale variable

3 REPLIES 3
andreas_lds
Jade | Level 19

Please add the expected result to your post. Some assumptions made:

- only months at the end are missing

- not only the fourth obs is added, but up to 3, so that there are four obs per id

- the source-data is sorted by id and month

 

data want;
   set have;
   by id;

   output;

   if last.id and month < 4 then do;
      do month = month + 1 to 4;
         sale = 0;
         output;
      end;
   end;
run;

 

 

Astounding
PROC Star

Assuming your data is in order as you have indicated:

 

data want;

set have;

by id;

output;

if last.id;

sale = 0;

if month < 4 then do month = month + 1 to 4;

  output;

end;

run;

 

A few seconds apart ... great minds think alike.

Kurt_Bremser
Super User
  1. set up a reference table for months
  2. with a cartesian join, build all possible combinations of id/month
  3. use that as base for a join with your original data, and set sale according to your rule
data months;
input month;
cards;
1
2
3
4
;
run;

proc sql;
create table lookup as
select distinct s.id, b.month
from
  s, months b
;
create table want as
select
  a.id,
  a.month,
  case
    when s.sale is not missing then s.sale
    else 0
  end as sale
from
  lookup a
  left join s
  on a.id=s.id and a.month=s.month
;
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
  • 3 replies
  • 777 views
  • 1 like
  • 4 in conversation