DATA Step, Macro, Functions and more

How to create Two output in excel

Reply
Contributor
Posts: 33

How to create Two output in excel

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 DescNumberspercent
1001Administrator419%
  Coordinator 419%
 Supervisor629%
 Manager210%
 Other (please specify)523%
Total Response 21 
 Comments  
 Hiring Supervisor  
 Hiring SV  
 I was a hiring supervisor.  
 Hiring Supervisor.  

 

Thanks,

 

PROC Star
Posts: 7,363

Re: How to create Two output in excel

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

 

Ask a Question
Discussion stats
  • 1 reply
  • 110 views
  • 0 likes
  • 2 in conversation