SAS Format contents WITHOUT running a proc or datastep (ie pure macro)

[robAs]

Easy to access format contents with proc format cntlout=work.myContents;    etc etc 
However I would like to get the contents in a 'pure macro' style,  ie So that I can access the 'start' value of a format WITHOUT having to run a proc or datasetp which limits the situations  I can run the macro.   Does a FCMP with run_macro allow this perhaps?

cheers  rob

[BrunoMueller]

Illustrate what you want to achieve with a simple format definition and how you plan to use this format within your macro would help to understand the question better and hopefully answer it.

[LinusH]

If you can keep a permanent version of your formats in a CNTLOUT table, you can use SAS File I/O functions to query it. Awkward, but it might fit your purpose?

[Kurt_Bremser]

The only practical way to retrieve all START values for a given format is PROC FORMAT. To get the values in any other way would involve reading the catalog file where the format is stored as a binary stream and extract the data, which would probably leave you with 10k of macro code.

[BrunoMueller]

If you really want to do this, there is a way using the DOSUBL function, it can be called within a macro, it will execute some code, but not produce any text. The resulting SAS data set is then read using functions. See this example below.

proc format;
  value $gender
    "F" = "female"
    "M" = "male"
    other = "others"
  ;
run;

%macro test;
  %local output rc dsid;
  %let output = _mydata;
  %let rc = %sysfunc(dosubl(%str(proc format cntlout=&output;run;)));  
  %let dsid = %sysfunc(open(&output));
  %syscall set(dsid);
  %do %while (%sysfunc(fetch(&dsid)) = 0) ;
    %put &fmtname &start &end &label;
  %end;
  %let rc = %sysfunc(close(&dsid));  
%mend;

%put NOTE: call %nrstr(%test) *%test*;

As you can see the call of the macro does not create any text, but it does show the contents of the SAS data set in the SAS log.

I am still interested in the use case of this.

[PaulMcDonald-AZ]

I took Bruno's idea and kind of went a little crazy with it...  Here's a macro that I put together that will return the results of a user declared format from any available format library.

%macro fmtlist (format) /des='returns all values of a format to a square bracket listing' ;
   %local format sblout fmtname liblist numlib i library cntlout startlst endlst hlolst

          labellst notes sascode rc start end hlo label dsid memname ;
   %let sblout = %sysfunc(getoption(missing)) ;

   %if %length(&format)=0 %then %put NOTE: Argument 1 to macro function %nrstr(%FMTLIST) is missing or out of range. ;
   %else %do ;
      %let format = %upcase(&format) ;
      %let fmtname = %sysfunc(compress(&format, %str($.))) ;

      %let liblist = %translate(%sysfunc(getoption(fmtsearch)), ' ', '()') ;
      %let numlib = %sysfunc(countw(&liblist)) ;

      %if &numlib = 0 %then

          %put NOTE: No libraries declared in SYSTEM OPTION FMTSEARCH=%sysfunc(getoption(fmtsearch)). ;
      %else %do i = 1 %to &numlib ;
         %let library = %scan(&liblist, &i, %str(%(%) )) ;

         %if %cexist(&library..formats) %then %do ;
            %let cntlout = %nextdata(work) ;
            %let startlst = %str() ;
            %let endlst = %str() ;
            %let hlolst = %str() ;
            %let labellst = %str() ;

            %let notes=%sysfunc(getoption(notes)) ;

            %let sascode = %str(options nonotes ;

                                proc format library=&library cntlout=&cntlout (where=(upcase(fmtname)="&fmtname")) ;

                                run ;

                                options &notes;) ;

            %let rc = %sysfunc(dosubl(%superq(sascode))) ;
            %if %obscnt(&cntlout) > 0 %then %do ;
               %let dsid = %sysfunc(open(&cntlout)) ;
               %syscall set(dsid) ;
               %do %while (%sysfunc(fetch(&dsid)) = 0) ;
                  %let startlst = &startlst {%strip(&start)} ;
                  %let endlst = &endlst {%strip(&end)} ;
                  %let labellst = &labellst {%strip(&label)} ;
                  %if %length(&hlo) = 0 %then %let hlolst = &hlolst {.} ;
                  %else %let hlolst = &hlolst {%strip(&hlo)} ;
               %end ;
               %let rc = %sysfunc(close(&dsid)) ;
               %let i = &numlib ;
               %let sblout = [LIBRARY: &library] [FMTNAME:%strip(&fmtname)] [START:%strip(&startlst)]

                             [END:%strip(&endlst)] [HLO:%strip(&hlolst)] [LABEL:%strip(&labellst)] ;

               %let memname = %scan(&cntlout, 2) ;
               %let sascode = %str(options nonotes ;

                                   proc datasets library=work nolist ;

                                      delete &memname ;

                                   quit ;

                                   options &notes ; ) ;
               %let rc = %sysfunc(dosubl(%superq(sascode))) ;
            %end ;
         %end ;
      %end ;
   %superq(sblout)
%mend fmtlist ;

Using Bruno's example format above, you could call it like this:
%put FORMATS=%fmtlist($gender) ;

The results are:
90 %put FORMATS=%fmtlist($gender) ;
FORMATS=[LIBRARY: WORK] [FMTNAME:GENDER] [START:{F} {M} {**OTHER**}] [END:{F} {M} {**OTHER**}] [HLO:{.} {.} {O}] [LABEL:{female} {male} {others}]

I put these into a "square bracket listing" where the list is parsed and grouped first into square brackets and can be labeled with a colon marker, then braces inside the square brackets to group each  component of the list.  It's not perfect, but can work.  Choose your own delimiters, it won't bug me.

You'll note that the returned value shows which LIBRARY the format was found, and it is searched in order declared in the FMTSEARCH= option.  This way, if you have two libraries with the same named format in them, the value you get back will be the one that your program session will find as it goes through the different libraries in order.

A few embedded macros I should explain:  %CEXIST is a variation of the CEXIST function and checks to confirm if a catalog exists.  %OBSCNT is my flavor of returning the observations of a data set.  %NEXTDATA returns the next availabe (unused) data set name in the given library (which is then deleted at the end of the macro).  %STRIP is simply a compressed version of %SYSFUNC(STRIP()), and %TRANSLATE is version of %SYSFUNC(TRANSLATE()).

In just about every case, I do think it would be easier-faster-better-simpler to use CNTLOUT= on PROC FORMAT, or otherwise search catalogs. But this will do. 

Your mileage my vary.

[Quentin]

Agree with Bruno's suggestion to consider using DOSUBL when you want to write a function-style macro with pure macro code, but want to execute DATA steps or PROC steps.  For an introduction to the magic of DOSUBL, the best starting point is Rick Langston's original paper: https://support.sas.com/resources/papers/proceedings13/032-2013.pdf.

Since you mention FCMP and run_macro, you might also be interested in earlier papers by Secosky https://support.sas.com/resources/papers/proceedings12/227-2012.pdf  and Rhoads https://www.lexjansen.com/nesug/nesug12/bb/bb14.pdf

[robAs]

Adding context to my original question:

We Use DBMS as a data source for sas analytical processes .
We may want to read/query any of 5000+ tables. Many of these DBMS tables contain a high number of columns with a column name >32 characters which is the SAS max column name length.

Here is a sample of column names over 32 chars:
accommodation_hospital_charge_quarterly

admission_weekly_item_scores_validation_report             

ancillary_downgrade_non_reported_revenue          

channel_agent_type_mapping_desc             

corporate_discount_policy_count  

cover_options_special_offers_preference

cover_options_special_offers_preference_email  

cover_options_special_offers_preference_phone                  

cover_options_special_offers_preference_sms     

The column names are excessively long because little attempt has been made in the dbms (snowflake) to keep the name short.

We wish to abbreviate these column names to maintain meaningful descriptions using a standardised and consistent set of rules to reduce the amount of variation.
For example the first of the above column names would abbreviate to
accom_hosp_chg_qtly
adm_wkly_item_scores_validtn_rpt 
etc etc

One common programming task is in the creation of views in snowflake. The analyst/programmer is currently hardcoding the abbreviated values such as CVR_OPTNS_SPCL_OFFRS_EML  (see example in red below)

EXAMPLE1
proc sql noerrorstop;
  connect using snowflak;
  execute ( 
           create or replace view edp.workspace._v_TabNameDeleteMeNow  as 
           select  
            CUSTOMER_ECN_NO  as CUSTOMER_ECN_NO
           ,COVER_OPTIONS_SPECIAL_OFFERS_PREFERENCE_EMAIL  as CVR_OPTNS_SPCL_OFFRS_EML
           ,COVER_OPTIONS_SPECIAL_OFFERS_PREFERENCE_SMS    as CVR_OPTNS_SPCL_OFFRS_SMS 
           ,COVER_OPTIONS_SPECIAL_OFFERS_PREFERENCE_PHONE  as CVR_OPTNS_SPCL_OFFRS_PHN 
           from edp.CUSTDATA.AEP_CUSTOMER_PERSONAL_DETAILS 
  ) by snowflak;
  disconnect from snowflak;
quit;

I would like to change the above by automatically deriving the abbreviated column name using a macro %abbreviate such as  below.
Note that this is 'in-line' right in the middle of a proc, therefore I cannot simply run a macro at this point which runs another proc or data step as it would give a syntax error)  

EXAMPLE2
proc sql noerrorstop;
  connect using snowflak;
  execute ( 
           create or replace view edp.workspace._v_TabNameDeleteMeNow  as 
           select  
            CUSTOMER_ECN_NO  as CUSTOMER_ECN_NO
           ,COVER_OPTIONS_SPECIAL_OFFERS_PREFERENCE_EMAIL  as %abbreviate(COVER_OPTIONS_SPECIAL_OFFERS_PREFERENCE_EMAIL )
           ,COVER_OPTIONS_SPECIAL_OFFERS_PREFERENCE_SMS    as %abbreviate(COVER_OPTIONS_SPECIAL_OFFERS_PREFERENCE_SMS)
           ,COVER_OPTIONS_SPECIAL_OFFERS_PREFERENCE_PHONE  as %abbreviate(COVER_OPTIONS_SPECIAL_OFFERS_PREFERENCE_PHONE )  
           from edp.CUSTDATA.AEP_CUSTOMER_PERSONAL_DETAILS 
  ) by snowflak;
  disconnect from snowflak;
quit;

I already have a SAS format in the format search path which contains hundreds of ‘accepted’ abbreviations of commonly used words used in the business which looks like this:

proc format library=xxxx.formats;
  value $abbreviations 
        'Accident'     =’Accdnt'
        'Accommodation'='Accm'
        'Additional'   ='Addnl'
        'Admitted’ = 'Admtd'
        'Admission'	= 'Adm'
        'Agenda'	= 'Agnda'
        'Agent'	='Agnt'
        'Ambulance'	='Amb'
        'Amount'	='Amt'
        'Ancillary'	='Anc'  
    Etc etc etc 

So my thought is to write a macro called %abbreviate :
%abbreviate(Longstring,FormatName);

1. Queries the format to get all the ‘start’ values of the format  which is the ‘long string’
2. Create a macro var indexed array   setting up macro vars1-n  for example &val1=Accident &val2=Accommodation  &val3=Additional etc etc
3. Loops through all the values  of &val1-n  to %sysfunc(TRANWRD) the ‘start’ values to the %sysfunc(PUTC) formatted value using the $abbreviations format. The tranwrd replaces all occurrences in the supplied string (ie the original column name) with the abbreviated value tp derive the short and meaningful value 😊

[Tom]

That does not sound like a task that requires a macro that does not run any SAS code.  

Instead you should create a macro that takes input the name of the schema or table you want to be able to use and have it generate code to create a view (or perhaps a dataset) to that table using the shorter names.  

Then the rest of the code just references that view.

So essentially you want a macro that can generate code like:

libname snowlib ..... connect to your schema in snowflake ....;
libname saslib 'some path on your SAS server';
proc sql;
connect using snowlib;
create view saslib.short_table_name as
select 
 short_name1 label='long_name1'
,short_name2 label='long_anme2'
from connection to snowlib
(select
 long_name1 as short_name1
,long_name2 as short_name2
from long_table_name
);
quit;

[BrunoMueller]

I agree with @Tom it would be better to create macro, that takes a tablename from Snowflake as input and then creates the corresponding SAS program code to create the view with the shorter names. You could use a DATA step to implement the logic. In the DATA step you write the code to a file which can then be executed using %INCLUDE ....; statement.

Find below an example for a macro that builds on your idea. It takes the individual parts of a long column name apart and uses a format to convert the individual parts to the short version. If a individual name can not be found in the format it simply stays the same.

proc format;
  value $abbreviations 
    'Accident'     = 'Accdnt'
    'Accommodation' = 'Accm'
    'Additional'   = 'Addnl'
    'Admitted' = 'Admtd'
    'Admission' = 'Adm'
    'Agenda'  = 'Agnda'
    'Agent' = 'Agnt'
    'Ambulance' = 'Amb'
    'Amount'  = 'Amt'
    'Ancillary' ='Anc'  
  ;
run;

%macro abbreviate(longName, format=$abbreviations, dlm=_);
  %local i nParts tempPart newName;
  %let nParts = %sysfunc(countw(&longName, &dlm));

  %do i = 1 %to &nParts;
    %let tempPart = %scan(&longname, &i, &dlm);
    %let newName = &newName%sysfunc(putc(&tempPart, &format));

    %if &i < &nParts %then %do;
      %let newName = &newname&dlm;
    %end;
  %end;

  %put NOTE: &sysmacroname &=longName &=newname;
  &longName as &newName
%mend;

%put NOTE: *%abbreviate(Accident_Admitted_Agenda_Amount)*;
%put NOTE: *%abbreviate(Acc_Admitted_Agenda_Admission_cnt)*;