BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Martinez77
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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

View solution in original post

11 REPLIES 11
data_null__
Jade | Level 19

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
Fluorite | Level 6

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__
Jade | Level 19

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

Martinez77
Fluorite | Level 6

Data_null_,

Thank you I figured it out shortly after I replied.

Tom
Super User Tom
Super User

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
Fluorite | Level 6

Tom,

Thank you that is very helpful I greatly appreciate it.

Martinez77
Fluorite | Level 6

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
Fluorite | Level 6

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
Fluorite | Level 6

Got it....%bquote

Thanks anyways

Tom
Super User Tom
Super User

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
Fluorite | Level 6

Thank you Tom I appreciate the response

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
  • 11 replies
  • 2045 views
  • 7 likes
  • 3 in conversation