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

Hi, I have an issue while applying multiple formats based on the variable values. I saw previous posts, but it was not helpful for me. Mistake might be from my end while applying proper formats. Please help me how to resolve this issue.

 

INPUT DATA:

============

TYPE      TOTALS

A              39442499

B             828727452.170335

B              519125592.249982

B             1491576104.14236

B              31241133938.0128

C            2026005

C              2949321.99999995

 

REQUIRED OUTPUT:

================

TYPE      TOTALS

A              39,442,499

B             $8,28,727,452.17

B              $ 519,125,592.25

B             $1,491,576,104.14

B              $31,241,133,938.01

C            2,026,005

C              2,949,322

 

I tried the code as below but I am getting missing values under totals variable in excel sheet.

 

ods tagsets.excelxp file='C:\Test\test.xls' style=sasweb;

 

proc report data=sum nowd;

Column TYPE TOTALS;

define TYPE/ display style(column)={just=l};

define TOTALS/computed style(column)={just=l};

 

compute TOTALS;

 

if x='B' then do;

*call define(_col_,'format','dollar19.2');

call define(_col_,'style','style={tagattr="Format:$#,##0.00"}');

end;

else do;

*call define(_col_,'format','comma12.');

call define(_col_,'style','style={tagattr="Format:#,###"}');

end;

endcomp;

run;

 

ods _all_ close;

 

 

PLEASE HELP ME ON THIS. THANKS IN ADVANCE.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

As @Cynthia_sas noted you should get a note in the LOG about trying to COMPUTE a variable with the same name as a variable on the input dataset.

NOTE: The computed variable TOTALS is also a data set variable.
NOTE: The output might not be as expected.

Making that change and referencing the TYPE variable your code should work.

data have ;
  input TYPE $ TOTALS ;
cards;
A 39442499
B 828727452.170335
B 519125592.249982
B 1491576104.14236
B 31241133938.0128
C 2026005
C 2949321.99999995
;

%let path=%sysfunc(pathname(work));
ods excel file="&path/test.xlsx";

proc report data=have nowd;
  column TYPE TOTALS;
  define TYPE/ display style(column)={just=l} width=4;
  define TOTALS/ display style(column)={just=l} width=19;
  compute TOTALS;
    if type='B' then do;
       call define(_col_,'format','dollar19.2');
       call define(_col_,'style','style={tagattr="Format:$#,##0.00"}');
    end;
    else do;
       call define(_col_,'format','comma12.');
       call define(_col_,'style','style={tagattr="Format:#,###"}');
    end;
  endcomp;
run;

ods excel close;
  TYPE               TOTALS
  A              39,442,499
  B         $828,727,452.17
  B         $519,125,592.25
  B       $1,491,576,104.14
  B      $31,241,133,938.01
  C               2,026,005
  C               2,949,322

image.png

View solution in original post

4 REPLIES 4
Cynthia_sas
SAS Super FREQ
Hi, your IF statement is testing X, it should be testing TYPE. Also, COMPUTED is the WRONG usage for TOTALS. Unless I misunderstand your data, it looks like your 2 variables are TYPE and TOTALS. Therefore, TOTALS is not new. You only use COMPUTED usage for a completely NEW variable. You can have a COMPUTE block on an existing variable. Please see page 9-10 of this paper http://support.sas.com/resources/papers/proceedings16/SAS5762-2016.pdf for a directly relevant example.

cynthia
Banu
Obsidian | Level 7
Cynthia, thank you so much for your suggestion. Attached doc is really helpful for me. Thank you.
Tom
Super User Tom
Super User

As @Cynthia_sas noted you should get a note in the LOG about trying to COMPUTE a variable with the same name as a variable on the input dataset.

NOTE: The computed variable TOTALS is also a data set variable.
NOTE: The output might not be as expected.

Making that change and referencing the TYPE variable your code should work.

data have ;
  input TYPE $ TOTALS ;
cards;
A 39442499
B 828727452.170335
B 519125592.249982
B 1491576104.14236
B 31241133938.0128
C 2026005
C 2949321.99999995
;

%let path=%sysfunc(pathname(work));
ods excel file="&path/test.xlsx";

proc report data=have nowd;
  column TYPE TOTALS;
  define TYPE/ display style(column)={just=l} width=4;
  define TOTALS/ display style(column)={just=l} width=19;
  compute TOTALS;
    if type='B' then do;
       call define(_col_,'format','dollar19.2');
       call define(_col_,'style','style={tagattr="Format:$#,##0.00"}');
    end;
    else do;
       call define(_col_,'format','comma12.');
       call define(_col_,'style','style={tagattr="Format:#,###"}');
    end;
  endcomp;
run;

ods excel close;
  TYPE               TOTALS
  A              39,442,499
  B         $828,727,452.17
  B         $519,125,592.25
  B       $1,491,576,104.14
  B      $31,241,133,938.01
  C               2,026,005
  C               2,949,322

image.png

Banu
Obsidian | Level 7
Had few mistakes in my code and then corrected. Now it's working as expected. Thanks for your help.

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
  • 4 replies
  • 2074 views
  • 0 likes
  • 3 in conversation