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 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.
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,
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.