BookmarkSubscribeRSS Feed
SasGuy614
Fluorite | Level 6

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.

2 REPLIES 2
art297
Opal | Level 21

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 380 views
  • 0 likes
  • 3 in conversation