Quote is macro variable

[Aman4SAS]

ok, Please let me know if i m missing any ods or goption?

[Patrick]

I agree with : If you could provide some representative sample data (a data step creating such data) and then tell/show us how the desired result should look like then we would be in a much better situation to provide support.

Also: Which version of SAS (under which OS) and version of Excel are you using?

[Aman4SAS]

hi,

please help. I m tired doing experiment to get my desired output. please suggest, below is my code

and also column values are not coming at center as i m trying make it in center but its happening,

ODS noresults;

ODS listing close;

ods tagsets.excelxp file="I:\anuj\temp folder\tmp.xls";

ods tagsets.excelxp options(sheet_name="FIRST SHEET");

Proc report data= tblsocc6_MSA nowd headline split="#"

style(report)=[cellspacing=2 borderwidth=1 bordercolor=blue]

style(header)=[color=black

               fontsize =1.5 textalign=center fontfamily = arial]

style(column)=[color=black

               fontfamily=helvetica fontsize=1 cellheight=11 pt ]

/*style(lines)=[color=white backgroundcolor=black

              fontstyle=italic fontweight=bold fontsize=5]*/

style(summary)=[color=cx3e3d73 backgroundcolor=cxaeadd9

                fontfamily=helvetica fontsize=3 textalign=r];

column ("(*ESC*)S={ textalign= left Font_weight=bold}   xyz (*ESC*)S={}"

areaname

("Management and Business/Financial"  (occ_tot_undup1 occ_mean_wage1))

("" (blank1))

("Professional & Related" (occ_tot_undup2 occ_mean_wage2))

("" (blank))

("Service" (occ_tot_undup3 occ_mean_wage3))) ;

define areaname/ display "Location" style(Header)=[just=center cellwidth=1.7 in] style(column)=[just=left];

define occ_tot_undup1/display "Total Ads # &currmon" format=comma10. style(Header)=[just=center cellwidth=.8 in] style(column)=[just=center];

define occ_tot_undup2/display "Total Ads # &currmon" format=comma10. style(Header)=[just=center cellwidth=.8 in] style(column)=[just=center];

define occ_tot_undup3/display "Total Ads # &currmon" format=comma10. style(Header)=[just=center cellwidth=.8 in] style(column)=[just=center];

define occ_mean_wage1/display "Average Hourly # Wage ~{super 2*}" format=dollar6.2 style(Header)=[just=center cellwidth=.8 in] style(column)=[just=center];

define occ_mean_wage2/display "Average Hourly # Wage ~{super 2*}" format=dollar6.2 style(Header)=[just=center cellwidth=.8 in] style(column)=[just=center];

define occ_mean_wage3/display "Average Hourly # Wage ~{super 2*}" format=dollar6.2 style(Header)=[just=center cellwidth=.8 in] style(column)=[just=center];

define blank1/ computed "" format= nodot. style(column)=[just=center cellwidth=.2 in] ;

define blank/ computed "" format= nodot. style(column)=[just=center cellwidth=.2 in] ;

compute blank1;

blank1=. ;

endcomp;

compute blank;

blank=. ;

endcomp;

compute areaname;

if Areaname = "United States" and _break_=' ' then

      call define(_row_, "style",

                  "style=[backgroundcolor=cxaeadd9

                          fontfamily=helvetica

                          fontweight=bold]");

endcomp;

compute after ;

line "(*ESC*)S={ textalign= left Font_weight=bold} (*ESC*)S={}";

line "(*ESC*)S={indent=1in }1. any strings    (*ESC*)S={}";

line "(*ESC*)S={indent=1in }2.any string [it can be 2 or 3 lines]  (*ESC*)S={}";

endcomp;

run;

quit;

ods tagsets.excelxp close;

ODS listing;

[Tom]

One way is to have SAS put normal quotes around the values using QUOTE() and then take them off later using DEQUOTE().

proc sql noprint;

  select quote(cats(area))

    into :all_list separated by '|'

    from aj.testcheck

  ;

  %let ct_all=&sqlobs;

  select quote(cats(area))

    into :st_list separated by '|'

    from aj.testcheck

    where length(area)=2

  ;

  %let ct_st=&sqlobs;

  select quote(cats(area))

    into :msa_list  separated by '|'

    from aj.testcheck

    where length(area) ne 2

  ;

  %let ct_maa=&sqlobs;

quit;

%macro fetch();

%do i=1 %to &ct_st;

  %let st=%qsysfunc(dequote(%scan(&st_list,&i,|)));

  %do j=1 %to &ct_all;

     %let area=%qsysfunc(dequote(%scan(&all_list,&j,|)));

  %end;

%end;

%mend;