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

Hallo zusammen,

 

Mit proc format erstelle ich ein individuelels Format, das ich final in meiner Ergebnistabelle haben möchte. Mit Proc Report geht das wunderbar mit ods excel durch bis zu der Exceldatei.

 

Proc odstabel ignoriert es. Hat jemand eine Idee?

 

proc format ;

value dezi0_fmt
. = ' — '
.a = ' • '
.b = ' X'
.c = ' /'
.d = ' '
0 = ' — '
other = [f16.0]
;
run;


data class;

format age weight height dezi0_fmt.;

set sashelp.class;

if _N_ = 2 then do;age =0;weight =0; height = 0;end;
if _N_ = 3 then age =.;
if _N_ = 4 then age =.b;
run;

 

title " " ;
footnote " ";
ods listing close;
ods html close;
ods excel file = "&M_output.class_TEST_b.xlsx"
options(
embedded_titles = "yes"
)


;

ods excel
options(
sheet_name="VGR-DL-Tab-05-A-Teil-1"
);

title1 bc=white j=l "Jahr: &M_jahr_ooc. Test";


proc odstable data=class;

column
age
sex
height
weight;


define age;
STYLE ={ tagattr='format:# ### ### ### ##0 ; - # ### ### ### ##0 ; "0 "' } ;
header='AGE OF STUDENT';
format=dezi0_fmt.;
end;


define sex;
header='GENDER';
style={fontsize=10pt just=l foreground=blue borderrightstyle=dashed};
end;

 

define weight;
header='WEIGHT';
format=dezi0_fmt.;
end;

define height;
header='HEIGHT';
STYLE ={ tagattr='format:# ### ### ### ##0 ; - # ### ### ### ##0 ; "0 "' } ;
format=dezi0_fmt.;

end;

run;


ods excel close;

 

lg

 

eric berger

 

1 ACCEPTED SOLUTION
4 REPLIES 4
Patrick
Opal | Level 21

Running your code under Windows with SAS9.4M7 appears to work for me.

proc format;
  value dezi0_fmt
    . = ' — '
    .a = ' • '
    .b = ' X'
    .c = ' /'
    .d = ' '
    0 = ' — '
    other = [f16.0]
  ;
run;

data class;
  format age weight height dezi0_fmt.;
  set sashelp.class;

  if _N_ = 2 then
    do;
      age =0;
      weight =0;
      height = 0;
    end;

  if _N_ = 3 then
    age =.;

  if _N_ = 4 then
    age =.b;
run;

%let M_output=c:\temp\;
%let M_jahr_ooc=xxx_;
title " ";
footnote " ";
ods listing close;
ods html close;

ods excel file = "&M_output.class_TEST_b.xlsx"
  options(
  embedded_titles = "yes"
  )
;
ods excel
  options(
  sheet_name="VGR-DL-Tab-05-A-Teil-1"
  );
title1 bc=white j=l "Jahr: &M_jahr_ooc. Test";

proc odstable data=class;
  column
    age
    sex
    height
    weight;
  define age;
    STYLE ={ tagattr='format:# ### ### ### ##0 ; - # ### ### ### ##0 ; "0 "' };
    header='AGE OF STUDENT';
    format=dezi0_fmt.;
  end;

  define sex;
    header='GENDER';
    style={fontsize=10pt just=l foreground=blue borderrightstyle=dashed};
  end;

  define weight;
    header='WEIGHT';
    format=dezi0_fmt.;
  end;

  define height;
    header='HEIGHT';
    STYLE ={ tagattr='format:# ### ### ### ##0 ; - # ### ### ### ##0 ; "0 "' };
    format=dezi0_fmt.;
  end;
run;

ods excel close;

Patrick_0-1708565020683.png

 

 

 

E_Berger
Fluorite | Level 6

Hallo Patrick,

 

das funktioniert. Vielen Dank! Das Problem waren die führenden blanks in dem Proc Format.

 

Eine andere Lösung, wenn man mit den führenden blanks im Proc Format arbeiten will,

ist die Angabe der Länge des Formats im define statement

 

define height;
header='HEIGHT';
STYLE ={ tagattr='format:# ### ### ### ##0 ; - # ### ### ### ##0 ; "0 "' };
format=dezi0_fmt16.;
end;

Gruß

eric berger

Patrick
Opal | Level 21

@E_Berger Good that you've got a solution. As you can see in my environment things worked even with the leading blanks. The screenshot from the Excel I've posted had been created with the exact code I've posted.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 569 views
  • 1 like
  • 3 in conversation