topic Re: Formatting macro variables in SAS Programming

Formatting macro variables

Martinez77 — Mon, 05 Aug 2013 20:00:48 GMT

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

Re: Formatting macro variables

data_null__ — Mon, 05 Aug 2013 20:05:34 GMT

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

Re: Formatting macro variables

Martinez77 — Mon, 05 Aug 2013 20:16:58 GMT

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

Re: Formatting macro variables

data_null__ — Mon, 05 Aug 2013 22:18:38 GMT

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

Re: Formatting macro variables

Tom — Mon, 05 Aug 2013 23:14:19 GMT

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,|);

Re: Formatting macro variables

Martinez77 — Mon, 05 Aug 2013 23:28:52 GMT

Tom,

Thank you that is very helpful I greatly appreciate it.

Re: Formatting macro variables

Martinez77 — Mon, 05 Aug 2013 23:29:57 GMT

Data_null_,

Thank you I figured it out shortly after I replied.

Re: Formatting macro variables

Martinez77 — Wed, 07 Aug 2013 20:28:01 GMT

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

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

Re: Formatting macro variables

Martinez77 — Wed, 07 Aug 2013 20:29:34 GMT

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

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

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

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

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.

Re: Formatting macro variables

Martinez77 — Wed, 07 Aug 2013 20:40:17 GMT

Got it....%bquote

Thanks anyways

Re: Formatting macro variables

Tom — Wed, 07 Aug 2013 20:42:54 GMT

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

Re: Formatting macro variables

Martinez77 — Wed, 07 Aug 2013 21:33:28 GMT

Thank you Tom I appreciate the response