topic DO Loops in SAS Programming

DO Loops

SasGuy614 — Fri, 25 Apr 2014 17:27:34 GMT

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.

Re: DO Loops

Reeza — Fri, 25 Apr 2014 19:19:24 GMT

38430 - Exporting BY groups from a SAS data set to multiple Microsoft Excel or text files

Re: DO Loops

art297 — Fri, 25 Apr 2014 19:52:40 GMT

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