DO Loops

Reply
Contributor
Posts: 29

DO Loops

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.

VERTICALProdTypeCOUNTPERCENTRowName
CORP_BANKINGCHK3626.086956521CORP_BANKING_CHK
CORP_BANKINGMMA_SAV3021.739130432CORP_BANKING_MMA_SAV
MIDDLE_MARKETCHK3626.086956523MIDDLE_MARKET_CHK
MIDDLE_MARKETMMA_SAV3626.086956524MIDDLE_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.

Super User
Posts: 17,868
PROC Star
Posts: 7,363

Re: DO Loops

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

Ask a Question
Discussion stats
  • 2 replies
  • 210 views
  • 0 likes
  • 3 in conversation