DATA Step, Macro, Functions and more

Enterprise Guide Formats not present in Stack Columns

Reply
Occasional Contributor
Posts: 15

Enterprise Guide Formats not present in Stack Columns

I am defining a format in a traditional SAS program for a variable using :

FORMAT L450 COMMA16.;

 When I use Stack Columns task, the format of the variable does not carry through.  Is there somewhere in the stack columns where I can specify the format for many different variables with many different formats?  Or is there another program I have to write to make this possible:

/* -------------------------------------------------------------------
   Code generated by SAS Task

   Generated on: Friday, April 28, 2017 at 12:12:48 PM
   By task: Stack Columns (2)

   Input Data: Local:WORK.AEACALC
   Server:  Local
   ------------------------------------------------------------------- */


/* Start of custom user code (Framework_BeforeTaskCode) */
<insert custom code here>
/* End of custom user code (Framework_BeforeTaskCode) */

%_eg_conditional_dropds(WORK.TRNSSTACKCOLUMNS_0000,
        WORK.TMP0TempTableInput,
        WORK.TMP1TempTableWork);
/* -------------------------------------------------------------------
   Sort data set WORK.AEACALC
   ------------------------------------------------------------------- */
PROC SORT
    DATA=WORK.AEACALC(KEEP=L447 L448 L449 L450 L451 L452 L453 L454 L455 L456 L457 L458 L459 L460 L461 L462 L463 L464 L465 L466 L467 L468 L469 L470 L471 L472 L473 L474 L475 L476 L477 L478 L479 L480 L481 L482 dist Name)
    OUT=WORK.TMP0TempTableInput
    ;
    BY dist Name
/* Start of custom user code (CC_Role_RoleBY) */
<insert custom code here>
/* End of custom user code (CC_Role_RoleBY) */


;
RUN;

PROC SQL;
    CREATE VIEW WORK.TMP1TempTableWork AS
    SELECT SRC.*, "Amount" AS _EG_IDCOL_
        FROM WORK.TMP0TempTableInput AS SRC;
QUIT;


/* Start of custom user code (BeforeProcTranspose) */
<insert custom code here>
/* End of custom user code (BeforeProcTranspose) */

PROC TRANSPOSE DATA = WORK.TMP1TempTableWork
    OUT=WORK.TRNSSTACKCOLUMNS_0000(LABEL="Stacked WORK.AEACALC")
    NAME=LineNumber
    LABEL=ValueDescription

/* Start of custom user code (InProcTranspose) */
<insert custom code here>
/* End of custom user code (InProcTranspose) */

    ;
    FORMAT 
        L449 COMMA16.
        L450 COMMA16.;
    LABEL 
        L449="L449";
    BY dist Name
/* Start of custom user code (CC_Role_RoleBY) */
<insert custom code here>
/* End of custom user code (CC_Role_RoleBY) */


;
    ID _EG_IDCOL_;
    VAR L447 L448 L449 L450 L451 L452 L453 L454 L455 L456 L457 L458 L459 L460 L461 L462 L463 L464 L465 L466 L467 L468 L469 L470 L471 L472 L473 L474 L475 L476 L477 L478 L479 L480 L481 L482
/* Start of custom user code (CC_Role_RoleVAR) */
<insert custom code here>
/* End of custom user code (CC_Role_RoleVAR) */


;

RUN;

PROC DATASETS LIB=WORK NOLIST;
    MODIFY TRNSSTACKCOLUMNS_0000;
    LABEL Amount = "Amount";
    LABEL LineNumber = "LineNumber";
    LABEL ValueDescription = "The label of the column from which the value came.";
RUN;

PROC SQL; DROP VIEW WORK.TMP1TempTableWork;
QUIT;

PROC DELETE DATA=WORK.TMP0TempTableInput;RUN;

/* -------------------------------------------------------------------
   End of task code
   ------------------------------------------------------------------- */
RUN; QUIT;

/* Start of custom user code (Framework_BeforeTaskHousekeepingCode) */
<insert custom code here>
/* End of custom user code (Framework_BeforeTaskHousekeepingCode) */

%_eg_conditional_dropds(WORK.TMP0TempTableInput,
        WORK.TMP1TempTableWork);
TITLE; FOOTNOTE;

/* Start of custom user code (Framework_AfterTaskCode) */
<insert custom code here>
/* End of custom user code (Framework_AfterTaskCode) */

Super User
Posts: 11,343

Re: Enterprise Guide Formats not present in Stack Columns

A column in a dataset may only have a single format associated with it. So the "STACK" may pick the format differently, possibly the topmost values format(?).

 

If want multiple values with diffent appearanc that can't be satisfied by a single format for the column then you may have to add a step to create a separate column and PUT the values with the format to get character values as desired. Or settle for the least offensive format and us proc datasets to set that after the transpose.

Occasional Contributor
Posts: 15

Re: Enterprise Guide Formats not present in Stack Columns

Thank you.  Evendually I create a List Report and each value has to have a specific decimal point length.  I may have to find another work around.

 

Capture.JPG

Super User
Posts: 11,343

Re: Enterprise Guide Formats not present in Stack Columns

Your example doesn't indicate what column may need different decimals or what they might look like. Note that if values that need different appearance fall within different ranges a custom format might do what you want. here is small code example to demostrate.

proc format library=work;
value custrange
0  - <10  = [f5.3]
10 - <100 = [f5.2]
100- <1000= [F5.1]
1000-<10000=[comma6.]
;
run;

data example;
   input x;
datalines;
0.456
1.345
4
9.23
10.235
66
88.4
100.28
222
345.5
777.777
1000
3333
4456.78
;
run;

proc print data=example noobs;
   var x;
   format x custrange.;
run;

 

 

Can you show what some of your starting data in worl.AEACALC would look like with some of the variable L447 etc, maybe 3 or 4 that will require the different decimals, what the final desired result using that reduced data would look like and maybe some business rules to generate the final outcome (which variables need sums or means or what have you).

 

Some times you just have to start over.

 

And must this be done using tasks? Sometimes code is needed or possibly quicker than a multiple step approach with the tasks.

Occasional Contributor
Posts: 15

Re: Enterprise Guide Formats not present in Stack Columns

I am not tied to tasks.  I would be happy with a code solution.  The attached excel file has the data that is produced from another program.  The data is then reported by each district.  I would like it so each variable can be put into a specific location in the printout with varying decimal lengths.  I would then produce a summary report for all districts for certain variables.  Here is what I would like the report to look like for the first district:

 

School1.JPG

Ask a Question
Discussion stats
  • 4 replies
  • 175 views
  • 0 likes
  • 2 in conversation