'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.;
... View more
