04-25-2014 01:27 PM
I have a table that has a unique identifier for each grouping of data that I would like to export to an excel file. The unique column in the table is called 'NAME'. The different groupings can be found below. This list will be changing and growing over the next few days to 40 - 50 so that is why I don't want to create multiple exports while using 40 - 50 where clause statements.
For my master data set I would like to create a do loop so that every time a new Name/Row is identified a proc export to an Excel file would be executed for those groupings of accounts. I would also like to include the "Name" to the Excel export.
How can I create something dynamic so that I can export all unique names separately from my master table with the "Name" of the grouping in the file name?
Any examples or suggestions would be greatly appreciated.
04-25-2014 03:52 PM
's suggested method will be needed if your Name variable contains any characters that aren't allowed in a SAS filename. And, like the following code, assumes that you license SAS/Access for PC File Formats, so that you can use the Excel engine.
The following code assumes, additionally, that your name variable contains strings that can be used directly as file names:
infile cards delimiter='09'x;
informat vertical $12.;
informat prodtype $8.;
informat name $30.;
input VERTICAL ProdType COUNT PERCENT Row Name;
MIDDLE_MARKET CHK 36 26.08695652 3 MIDDLE_MARKET_CHK
CORP_BANKING CHK 36 26.08695652 1 CORP_BANKING_CHK
MIDDLE_MARKET MMA_SAV 36 26.08695652 4 MIDDLE_MARKET_MMA_SAV
CORP_BANKING MMA_SAV 30 21.73913043 2 CORP_BANKING_MMA_SAV
CORP_BANKING CHK 36 26.08695652 5 CORP_BANKING_CHK
data temp (index=(Name));
data temp (drop=_BankNum);
if First.Name then _BankNum+1;
proc sql noprint;
select distinct cat('libname ',strip(BankNum),' Excel "c:\art\',
into :libnames separated by ' '
:cleanup separated by ' '
proc sql noprint;
select distinct catt("'",Name,"'")
into :varnames separated by ','
where libname='WORK' and
upcase(Name) ~in ('BANKNUM','FNAME')
dcl hash hh ( );
hh.definekey ('k' );
do k = 1 by 1 until (last.Name);
hh.output (dataset: FName);