How to create all months in the SAS report

[chanduk]

I have a data set which contains Month,avg_days_open variables . In month variable there is 1 and 11 month values are there . I would like to display in the report for  1 and 11 months avg_days_open are 60 ,70 and for the remaining months ( 2,3,4,5,6,7,8,9,10,12) should be avg_days_open are 0 in the report. How we can write the sas code for this requirement ( There is no month values 2,3,4,5,6,7,8,9,10,12 in the data set but should display in the report)?

[Reeza]

What kind Report? What does your code look like so far? 

Search: PRELOADFMT 

[chanduk]

I am generating report using proc report. The report should be like below.

Month            Average_Days_open

January 2016        60

February 2016      0

-----

----

July 2016              0

August 2016          0

........

----

November 2016    70

December 2016    0

I have a data in the final dataset for  January 2016 & November 2016 and remaing months data don't have in the data set. How to add the remaining months(February2016 ,March 2016..............) to the final data set and the values(Average_Days_open)  should be 0  in the report? My final data set contains variables like Month,Average_Days_open . Please let me know if you have any questions.

Thanks,

chandu

[chanduk]

I am generating report using proc report. The report should be like below.
Month Average_Days_open
January 2016 60
February 2016 0
-----
----
July 2016 0
August 2016 0
........
----
November 2016 70
December 2016 0

I have a data in the final dataset for January 2016 & November 2016 and remaing months data don't have in the data set. How to add the remaining months(February2016 ,March 2016..............) to the final data set and the values(Average_Days_open) should be 0 in the report? My final data set contains variables like Month,Average_Days_open . Please let me know if you have any questions.

Thanks,
chandu

[RW9]

Post test data, in the form of a dataset of what you have.  And post and example of what you wan that test data output to look like.  It should be simply a loop and output control in a datastep before your report - just guessing your data here as you have not posted it:

data have;
  month_yr="January 2016"; res=70; output;
  month_yr="November 2016"; res=9; output;
run;

data want (drop=i);
  set have;
  if month_yr="January 2016" then do;
    output;
    do i="Febuary","March","April","May","June","July","August","September";
      month_yr=catx(" ",i,"2016");
      res=0;
      output;
    end;
  end;
  else output;
run;

[chanduk]

Thanks for providing quick answer. One samll change. In our reporting the financial year starts from July to June. I would like the report should be like below
Month Average Days Open
July 2015 0
August 2015 0
September 2015 0
october 2015 0
November 2015 70
December 2015 0
January 2016 60
February 2016 0
------
------
June 2016 0

Please provide solution for this and also please remember the data set data also will change periodically ( For one month run having 1 & 11 months have data and another month run the data set having 2 & 10 months data .This time the remaing months values (avg_day_open) should be 0.)

[RW9]

Slightly different method then.  You create a template dataset which contains your year, then merge on the data that you actually have.  That way the template doesn't change. but the merging of different months each time does:

data template;
  length month_yr $20;
  do month_yr="July","August","September","October","November","December","January","Febuary","March","April","May","June";
    month_yr=catx(" ",month_yr,"2016");
    output;
  end;
run;

data have;
  month_yr="September 2016"; value=90; output;
  month_yr="January 2016"; value=40; output;
run;

proc sql;
  create table WANT as
  select  A.MONTH_YR,
          coalesce(B.VALUE,0) as VALUE
  from    TEMPLATE A
  left join HAVE B
  on      A.MONTH_YR=B.MONTH_YR;
quit;

[chanduk]

Thank you very much for giving solution.

[Reeza · Accepted solution]

@chanduk Please make sure to mark the appropriate response as the correct answer. 

[Reeza]

@chanduk You should mark @RW9 solution as correct, not my request to choose a correct solution!