topic Re: How to create all months in the SAS report in SAS Programming

How to create all months in the SAS report

chanduk — Fri, 27 Jan 2017 05:45:21 GMT

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)?

Re: How to create all months in the SAS report

Reeza — Fri, 27 Jan 2017 07:08:17 GMT

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

Search: PRELOADFMT

Re: How to create all months in the SAS report

chanduk — Fri, 27 Jan 2017 07:27:58 GMT

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

Re: How to create all months in the SAS report

chanduk — Fri, 27 Jan 2017 07:32:40 GMT

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

Re: How to create all months in the SAS report

RW9 — Fri, 27 Jan 2017 09:30:47 GMT

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;

Re: How to create all months in the SAS report

chanduk — Fri, 27 Jan 2017 10:57:08 GMT

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

Re: How to create all months in the SAS report

RW9 — Fri, 27 Jan 2017 11:21:57 GMT

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;

Re: How to create all months in the SAS report

chanduk — Fri, 27 Jan 2017 12:08:12 GMT

Thank you very much for giving solution.

Re: How to create all months in the SAS report

Reeza — Mon, 30 Jan 2017 00:19:05 GMT

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

Re: How to create all months in the SAS report

Reeza — Mon, 30 Jan 2017 01:27:46 GMT

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