Generate report in excel with product level wise and all the available month by using proc report

[Itharaju]

Hello Team ,

sample dataset  and Report  structure is attached here .

I have above requirement and need to create below report.

here we have 12 product like A,B and C ect. and i need to print each product in individual sheets like

if PRODUCT A then it should be in (sheet A) with only product A and  with All Available months like(01mar2020,01apr2020) in report structure.

if PRODUCT B then it should be in (sheet B) with only product B and  with All Available months like(01mar2020,01apr2020) in report structure..

Thanks in advance for your help on this Problem.

[Kurt_Bremser]

Since you already have an Excel spreadsheet, you should ask this question in a Microsoft-oriented forum, as it will involve VBA programming.

[Oligolas]

Hi,

I assume the report structure you described was only an example, otherwise you could maybe explain how you get 12 rows with different amounts in your desired report structure out of 9 rows in your sample data set.

Try this out:

proc datasets lib=work kill nolist;run;quit;
proc format; 
  picture cstmdt (default=10)
    low - high = '%0D. %b %y' (datatype=date);
run; 

libname xl xlsx "C:\Users\&sysuserid.\Desktop\have.xlsx";

data have;
   length date $10;
   set xl.'Sheet1$D7:H16'n(rename=(amount1=amount1_xl amount2=amount2n_xl));
   length amount1 amount2 $12;
   *adjust formatting to get the desired output - keep printable characters for month etc;
   month=compress(month,,'kaid');
   monthn=input(month,date9.);
   date=put(monthn,cstmdt.);
   amount1=strip(put(amount1_xl,best.));
   amount2=strip(put(amount2n_xl,best.));
   productC=compress(product,,'kaid');
   drop amount1_xl amount2n_xl;
run;

proc sort data=have out=work0; by productC monthn; run;

data work1;
   set work0;
   by productC monthn;
   *adjust formatting to get the desired output;
   retain count1 count2 value .;
   date1=date;
   if first.productC then count2+1;
   if first.monthn then do;
      value=1;
      count1+1;
      date='';
      output;
      call missing(product,month,flag,amount1,amount2);
      date=date1;
      output;
      call missing(date,value);
      output;
      output;
   end;
   else do;
      date='';
      output;
   end;
   drop date1;

   *write out;
   if last.productC then do;
      call execute('libname xl2 xlsx "C:\Users\&sysuserid.\Desktop\result.xlsx";');
      call execute('PROC SORT data=work1 out=xl2.sheet'||strip(put(count2,best.))||'(drop=monthn count1 count2 productC value); where productC eq "'||strip(productC)||'"; by count1 descending date descending value monthn product flag; run;');
      call execute('libname xl2 clear;');
   end;
run;

libname xl clear;