Formatting macro variables

[Martinez77]

So I have automated a reporting process which runs proc tabulate tables for each group by a sub group. In order to have the macro variable scan I had to compress the sub group to remove spacing(for obvious reasons).

Is there a way I can format those variables in the sub group to show spacing in the proc tabulate? Not sure how that proc format could work

The blue line is what I would probably need to change. For example, if &dept finds = "youwin" then in the table show "you win"

Here is my code:

proc sql noprint;

   select distinct STU_ACAD_ORG_L_DESC

   into :alldept separated by ' '

   from &DSN;                             *\ creates a macro variable list for each specific value for a variable *;

   select count(distinct STU_ACAD_ORG_L_DESC)           

   into :mycount

   from &DSN;

  quit;                                    *\ counts each variable in the list providing a total (this is needed to use the scan and do statements)*;

%put &alldept;        *\this shows how the macro variable list reads in the log (so that you can verify it is correctly formatted*;

%put &mycount;

%do i=1 %to &mycount;                *\ these two lines of code create a readable macro variable list and iteration*;

  %let dept=%scan(&alldept,&i,' ');  *\ the scan variable makes sas scan each macro variable in the list i.e. a loop*;        

  %put &dept;

        ods tagsets.ExcelXP OPTIONS(SHEET_INTERVAL="none" SHEET_NAME="&depT" embedded_titles='YES');

TITLE1 "&XOFFICE(&XPROG)";

TITLE2  "&DSN";

Title3 "&depT";

TITLE4 "Department by Admit by Year";

*TITLE5 "&DATE";

PROC TABULATE DATA = &DSN;

where STU_ACAD_ORG_L_DESC="&dept";

CLASS STU_ACAD_ORG_L_DESC ADMIT_N_DESC;

Class ACAD_TERM_DESC;

classlev stu_acad_org_l_desc /style=[outputwidth=5in];       *\ column with is changed for using this*;

TABLE ADMIT_N_DESC='Admit Status' ALL='TOTAL', acad_term_desc='Semester';

KEYLABEL N=' ';

RUN;

Thanks,

Martinez

[data_null__ · Accepted solution]

Why not use a delimiter that will allow your list to contain embedded blank.

Or put the list into macro-array.

or gen the code with a data step and %INC

[Martinez77]

Those sound like great Ideas that I was unaware I could do :smileyblush:.  I am still pretty new to SAS, sorry. How can I go about using a delimiter that will allow my list to contain embedded blank?

I already am researching the macro-array option.

Thank you so much for your help!,

Martinez

[data_null__]

change this separated by ' ' to a character other than blank that will not appear in the "words".  Perhaps the PIPE '|'

[Martinez77]

Data_null_,

Thank you I figured it out shortly after I replied.

[Tom]

Change the delimiter used to separate the department names. Also note that you should not include the quotes in the call to %SCAN() as they will be used as delimiters and if any department name ("Robin's Team") includes a quote then the %SCAN() will not work right.

Also you do not need run another query to find the count as PROC SQL has already put the value into SQLOBS macro variable.

proc sql noprint;

   select distinct STU_ACAD_ORG_L_DESC

     into :alldept separated by '|'

     from &DSN

   ;  

%let mycount = &sqlobs ;

quit;

  %let dept=%scan(&alldept,&i,|);                     

[Martinez77]

Tom,

Thank you that is very helpful I greatly appreciate it.

[Martinez77]

So I tried using the first technique, however, I get this error

HELP...

MPRINT(DEPARTMENT):   *\ counts each variable in the list providing a total (this is needed to

use the scan and do statements)*;

MLOGIC(DEPARTMENT):  %PUT &alldept

SYMBOLGEN:  Macro variable ALLDEPT resolves to Biological Sciences|Chemistry|College of Arts

            and Sciences|Communications Studies|Dance and Theatre|Economics|English|Foreign

            Languages & Literature|Geography|History|Linguistics & Tech

            Comm|Mathematics|Philosophy & Religion Studies|Physics|Political

            Science|Psychology|Radio/Television/Film|Speech & Hearing Sciences|World Lang,

            Lit, and Cultures

[Martinez77]

Sorry this is the entire code,

MPRINT(DEPARTMENT):   *\ counts each variable in the list providing a total (this is needed to

use the scan and do statements)*;

MLOGIC(DEPARTMENT):  %PUT &alldept

SYMBOLGEN:  Macro variable ALLDEPT resolves to Biological Sciences|Chemistry|College of Arts

            and Sciences|Communications Studies|Dance and Theatre|Economics|English|Foreign

            Languages & Literature|Geography|History|Linguistics & Tech

            Comm|Mathematics|Philosophy & Religion Studies|Physics|Political

            Science|Psychology|Radio/Television/Film|Speech & Hearing Sciences|World Lang,

            Lit, and Cultures

Biological Sciences|Chemistry|College of Arts and Sciences|Communications Studies|Dance and

Theatre|Economics|English|Foreign Languages & Literature|Geography|History|Linguistics & Tech

Comm|Mathematics|Philosophy & Religion Studies|Physics|Political

Science|Psychology|Radio/Television/Film|Speech & Hearing Sciences|World Lang, Lit, and

Cultures

MPRINT(DEPARTMENT):   *\this shows how the macro variable list reads in the log (so that you

can verify it is correctly formatted*;

MLOGIC(DEPARTMENT):  %PUT &mycount

SYMBOLGEN:  Macro variable MYCOUNT resolves to 19

19

SYMBOLGEN:  Macro variable MYCOUNT resolves to 19

MLOGIC(DEPARTMENT):  %DO loop beginning; index variable I; start value is 1; stop value is 19;

      by value is 1.

MPRINT(DEPARTMENT):   *\ these two lines of code create a readable macro variable list and

iteration*;

MLOGIC(DEPARTMENT):  %LET (variable name is DEPT)

SYMBOLGEN:  Macro variable ALLDEPT resolves to Biological Sciences|Chemistry|College of Arts

            and Sciences|Communications Studies|Dance and Theatre|Economics|English|Foreign

            Languages & Literature|Geography|History|Linguistics & Tech

            Comm|Mathematics|Philosophy & Religion Studies|Physics|Political

            Science|Psychology|Radio/Television/Film|Speech & Hearing Sciences|World Lang,

            Lit, and Cultures

SYMBOLGEN:  Macro variable I resolves to 1

ERROR: Macro function %SCAN has too many arguments.  The excess arguments will be ignored.

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric

       operand is required. The condition was: Lit

ERROR: Argument 2 to macro function %SCAN is not a number.

ERROR: The macro DEPARTMENT will stop executing.

MLOGIC(DEPARTMENT):  Ending execution.

[Martinez77]

Got it....%bquote

Thanks anyways

[Tom]

One or more of the department names contains a comma.

So you need to "protect" them so that %SCAN() does not treat them as parameter separators.

Change to: 

%let dept=%scan(%superq(alldept),&i,|);

Example Log:

605

606  %let alldept=a,b|c ;

607  %let dept=%scan(&alldept,1,|);

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: b|c

ERROR: Argument 2 to macro function %SCAN is not a number.

608  %put dept=&dept;

dept=

609

610  %let alldept=a,b|c ;

611  %let dept=%scan(%superq(alldept),1,|);

612  %put dept=&dept;

dept=a,b

[Martinez77]

Thank you Tom I appreciate the response