topic Re: How to create Two output in excel in SAS Programming

How to create Two output in excel

tekish — Wed, 29 Mar 2017 15:52:31 GMT

Dear EXPERTS,

i WANT TO CREATE AN EXCEL OUT PUT WITH PERCENTAGES AND COMMENTS UNDERNEATH.

HERE IS THE SAMPLE DATASET

Data test;

length question 4 choice $35 comment $200;

retain question choice comment;

input question choice$ 6-27 comment$ 29-55;

cards;

1001 Administrator

1001 Coordinator

1001 Supervisor

1001 Manager

1001 Other(please specify) Hiring Supervisor.

1001 Other(please specify) Hiring SV.

1001 Other(please specify) I was a hiring supervisor.

1001 Other(please specify) Hiring Manager.

1001 Other(please specify) Hiring Supervisor.

run;

Desired output

Questions#	Choice Desc	Numbers	percent
1001	Administrator	4	19%
	Coordinator	4	19%
	Supervisor	6	29%
	Manager	2	10%
	Other (please specify)	5	23%
Total Response		21
	Comments
	Hiring Supervisor
	Hiring SV
	I was a hiring supervisor.
	Hiring Supervisor.

Thanks,

Re: How to create Two output in excel

art297 — Thu, 30 Mar 2017 00:37:15 GMT

I don't think anyone has responded because SAS doesn't have a direct way of doing what you want.

However, three of us who often respond here created a macro that I think does exactly what you want. You can download the macro (there is no charge) at: http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

Here is the code I just tested to do what I think you are trying to do. Of course, it won't run as is unless you already have a directory 'c:\art' established and have run the exportxl macro:

data test;
  length question 4 choice $35 comment $200;
  retain question choice comment;
  input question choice$ 6-27 comment$ 28-55;
  cards;
1001 Administrator
1001 Administrator
1001 Administrator
1001 Administrator
1001 Coordinator
1001 Coordinator
1001 Coordinator
1001 Coordinator
1001 Supervisor
1001 Supervisor
1001 Supervisor
1001 Supervisor
1001 Supervisor
1001 Supervisor
1001 Manager
1001 Manager
1001 Other(please specify) Hiring Supervisor.
1001 Other(please specify) Hiring SV.
1001 Other(please specify) I was a hiring supervisor.
1001 Other(please specify) Hiring Manager.
1001 Other(please specify) Hiring Supervisor.
;

proc freq data=test;
  tables choice/ out=need1;
run;

data _null_;
  set need1 nobs=nobs;
  nobs+3;
  if _n_ eq 1 then call symput('nobs',catt('A',nobs));
  stop;
run;
%put &nobs.;

data need2 (keep=comment);
  set test (where=(not missing(comment)));
run;

%exportxl( data=need1,
           outfile=c:\art\testcombo.xlsx,
           sheet=test)

%exportxl( data=need2,
           outfile=c:\art\testcombo.xlsx,
           type=M,
           range=&nobs,
           sheet=test)

HTH,

Art, CEO, AnalystFinder.com

Re: How to create Two output in excel

VDD — Sun, 16 Sep 2018 17:54:08 GMT

this is piece of SAS code that I use to control my output to Excel. Not sure if this assist in your request, but maybe it has something in the EXCEL part that will.

/* This macro creates a dataset with all the field's of the old dataset.
      A where claus is used to obtain only those records needed.
      The dataset is ordered by two fields.  */
%macro sqlcreat(olddata=,newtable=,wvar=,wvalue=,obone=,obtwo=);
   proc sql;
      create table &newtable. as
      select *
      from &olddata.
      where &wvar. = &wvalue.
      order by &obone., &obtwo.;
   quit;
%mend;
/* This macro will create a new dataset in a sorted order, or it will
      sort a dataset if the newset value is equal to the olddata value. */
%macro sortone(olddata=,newset=,byvar=);
   proc sort data=&olddata. out=&newset.;
      by &byvar.;
   run;
%mend;
/* This macro is used to merge a dataset named dummy (who's number of fields and
      records are equal to the largest number of possible result's for the 
      old dataset.) */
%macro mergeit(new_ds=,old_ds=,byvar=);
   data &new_ds.;
      merge dummy(in=in_t)
            &old_ds.(in=in_f);
      by &byvar.;
   run;
%mend;

options noxwait noxsync mlogic;
X "C:\Program Files\Microsoft Office\Office\EXCEL.EXE";
data _null_;
   x=sleep(5);
run;
filename cmds dde 'excel|system';
data _null_;
   length mystuff $250;
   file cmds;
   pre_book = '[open("'||"&temppath.\&workbook."||'")]';
   put pre_book;
run;
filename outpre dde
"Excel|&temppath.\[&workbook.]&sheet.!r1c1:r561c10" notab;

/* put the information into the cells in the worksheet */
data _null_;
   file outpre notab;
   put '09'x'09'x'09'x"&mytitle.";
   set head_date; /* contains the results in a SAS dataset */
   file outpre notab;
   put '09'x'09'x'09'xprt_month", " prt_year;
   put /* results from the macros output that is in datasets */;
run;