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

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

Itharaju — Tue, 22 Sep 2020 04:05:54 GMT

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.

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

Kurt_Bremser — Tue, 22 Sep 2020 09:27:18 GMT

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

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

Oligolas — Fri, 25 Sep 2020 09:02:54 GMT

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;