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.
VERTICAL | ProdType | COUNT | PERCENT | Row | Name |
CORP_BANKING | CHK | 36 | 26.08695652 | 1 | CORP_BANKING_CHK |
CORP_BANKING | MMA_SAV | 30 | 21.73913043 | 2 | CORP_BANKING_MMA_SAV |
MIDDLE_MARKET | CHK | 36 | 26.08695652 | 3 | MIDDLE_MARKET_CHK |
MIDDLE_MARKET | MMA_SAV | 36 | 26.08695652 | 4 | MIDDLE_MARKET_MMA_SAV |
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.
'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:
data have;
infile cards delimiter='09'x;
informat vertical $12.;
informat prodtype $8.;
informat name $30.;
input VERTICAL ProdType COUNT PERCENT Row Name;
cards;
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));
set have;
run;
data temp (drop=_BankNum);
set temp;
by Name;
if First.Name then _BankNum+1;
BankNum=catt('_',put(_BankNum,z7.));
FName=catx('.',BankNum,name,'xlsx');
run;
proc sql noprint;
select distinct cat('libname ',strip(BankNum),' Excel "c:\art\',
strip(Name),'.xlsx";'),
catx(' ','libname',strip(BankNum),'clear;')
into :libnames separated by ' '
:cleanup separated by ' '
from temp;
;
quit;
&libnames.;
proc sql noprint;
select distinct catt("'",Name,"'")
into :varnames separated by ','
from dictionary.columns
where libname='WORK' and
memname='TEMP' and
upcase(Name) ~in ('BANKNUM','FNAME')
;
quit;
data _null_;
dcl hash hh ( );
hh.definekey ('k' );
hh.definedata (&varnames.);
hh.definedone ();
do k = 1 by 1 until (last.Name);
set temp;
by name;
hh.add ();
end;
hh.output (dataset: FName);
run;
&cleanup.;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.