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

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

View solution in original post

9 REPLIES 9
Reeza
Super User

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

 

Search: PRELOADFMT 

chanduk
Obsidian | Level 7

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
Obsidian | Level 7
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
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7
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
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7
Thank you very much for giving solution.
Reeza
Super User

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

Reeza
Super User

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

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
  • 9 replies
  • 1862 views
  • 2 likes
  • 3 in conversation