I have an excel file in which two of the fields are 'Area' and 'Chart_Field_1'. I would like each Area to be exported to an individual Excel file, and for each Chart_Field_1 within that Area to have its own sheet. Any suggestions are greatly appreciated. Thanks.
You might end up with different export statements for each area/chart field option. In the current version of SAS, you can send data to the same file but different sheets by using the "sheet=" option.
something like
proc export data=srcdata (where=(area='Area1' and chart_field_1='c1')) file=a1 dbms=xlsx replace; sheet='c1';
proc export data=srcdata (where=(area='Area1' and chart_field_1='c2')) file=a1 dbms=xlsx replace; sheet='c2';
proc export data=srcdata (where=(area='Area2' and chart_field_1='c1')) file=a2 dbms=xlsx replace; sheet='c1';
proc export data=srcdata (where=(area='Area2' and chart_field_1='c2')) file=a2 dbms=xlsx replace; sheet='c2';
I have a large number of variables so I'll need to go the macro route.
Not sure what I'm doing, this is what I have so far. What am I doing wrong?
PROC IMPORT OUT= WORK.IDs DATAFILE= "G:\FinUnitReporting\IFASDEMIScombined.xlsx"
DBMS=xlsx REPLACE;
SHEET="sheet1";
GETNAMES=YES;
RUN;
proc sql;
create table RawData as
select *
from work.IDs;
*;
%macro rpt(area);
Proc sql;
Create table t1 as
Select * from RawData
Where area = “&area.”;
%mend;
*;
%put _user_;
/* for testing
%rpt(CF1);
*/
/*************************************************************
loop processing
**************************************************************/
*;
proc sql;
create table grp1 as
select distinct area
from RawData
order by 1;
quit;
filename loops TEMP;
DATA _NULL_;
FILE LOOPS;
SET grp1
PUT '%rpt (' area ');' / '*;';
run;
*;
%inc loops;
*;
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!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.