topic Re: Quote is macro variable in SAS Programming

Quote is macro variable

Aman4SAS — Tue, 29 Jul 2014 09:12:24 GMT

I have a dataset (attached).

I want to create a macro variable combine all area separated by "|".

I used :

proc sql noprint;

select count(*), area into :ct_all, :all_list separated by "|" from aj.testcheck ;

select count(*), area into :ct_st, :st_list separated by "|" from aj.testcheck where length(area)=2;

select count(*), area into :ct_msa, :msa_list separated by "|" from aj.testcheck where length(area) ne 2; Problem here: length crossed the limit of 252.

quit;

%macro fetch();

%do i=1 %to &ctst;

%let st=%scan(%quote(&st_list), &i , %str(|));

%do j=1 %to &allnum;

%let area=%scan(%bquote(&all_list), &j , %str(|)); problem if i use quote then error comes "very few paramter for function scan" and if i use bquote then it stopped by one of area "Coeur d'Alene, ID".

%end;

%mend;

please suggest me . Thanks in advance.

Re: Quote is macro variable

RW9 — Tue, 29 Jul 2014 09:28:39 GMT

Hi,

What's your intended use? Personally I tend to avoid long macro variables in favour of storing in datasets. So say for instance I want to produce a print separately for each of those areas I could do:

data _null_;

set testcheck;

call execute('proc print data=some_data (where=(area="'||strip(area)||'")); run;');

run;

This would generate a proc print for each observation in testcheck with the restriction.

You could also do your macro call with the area:
%macro your_macro (a=);

... do something;

%mend your_macro;

data _null_;

set testcheck;

call execute(%nstr('%your_macro(a='||strip(area)||');');

run;

Re: Quote is macro variable

Aman4SAS — Tue, 29 Jul 2014 09:47:46 GMT

I need to fetch names to create separate files for each area.

attached dataset is been created from large dataset where 10 years sales record for each area,

can i handle that problem which i mentioned in my origional post.

Re: Quote is macro variable

RW9 — Tue, 29 Jul 2014 10:03:29 GMT

Its just a matter of slightly altering your original code:

%macro fetch (area=);

data some_new_dataset_&area.;

set xyz;

run;

%mend fetch;

data _null_;

set aj.testcheck;

call execute(%nstr('%fetch(area=('||strip(area)||');');

run;

For the way you were doing it your going to run in to various problems as your data has commas and quotes in and hence you are trying to force it by quoting/unquoting.

Re: Quote is macro variable

Aman4SAS — Tue, 29 Jul 2014 10:35:11 GMT

sorry , its not working .

:asking for one more "("

: "The function NSTR is unknown, or cannot be accessed."

if i changed to NRSTR then : ERROR: Macro parameter contains syntax error.

please suggest.

Re: Quote is macro variable

RW9 — Tue, 29 Jul 2014 10:42:39 GMT

Yep, sorry, typo there:

%macro fetch (area=);

data some_new_dataset_&area.;

set sashelp.cars;

run;

%mend fetch;

data _null_;

area="AJ";

call execute(%nrstr('%fetch(area='||strip(area)||');'));

run;

Re: Quote is macro variable

Tom — Tue, 29 Jul 2014 12:09:38 GMT

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;

%mend;

Re: Quote is macro variable

Patrick — Tue, 29 Jul 2014 12:32:55 GMT

If you need the values as part of filenames then wouldn't you need to get rid of invalid characters for such filenames like quotes and the like? If so then things could become quite simple as you wouldn't have to deal with "special" characters and unbalanced quotation marks anymore.

If you say "files": Will these be SAS data sets (limit is a length or 32 characters) or some external files?

Re: Quote is macro variable

Aman4SAS — Tue, 29 Jul 2014 14:03:05 GMT

no that will not be in use as a file name. It will use as a sheet name. but i neet to first fetch that and use in condition to filter the data.

and apart that , i need your help for one more addition task ,

i am creating a excel through

ODS listing close;

ods html body="I:\anuj\temp folder\tmp.xls";

can i create these excel with multiple sheet, as excel for north with sheets x ,y, z(related states).

please donot suggest ods tagsets.excelxp , its not working perfectly at my end.

thanks

Re: Quote is macro variable

RW9 — Tue, 29 Jul 2014 14:12:56 GMT

Sorry, why is ods tagsets.excelxp not working? I would personally strongly recommend that over the html as you have far more control over Excel options. Also, html doesn't have the concept of sheets as Excel does, so you will struggle to get that to work.

Re: Quote is macro variable

Aman4SAS — Tue, 29 Jul 2014 15:04:34 GMT

i m facing many challanges with tagsets.

few are:

its prompting to save file.

its not giving desired output file.

it not proving suporting version of excel file

and main issue is , its doing strange behaviour. providing missing missing value of computed variable for particular record for date 01jan2014.

in the same place rest else output giving complete values.

please suggest, i m looking for fix format for my all sheets.

Re: Quote is macro variable

RW9 — Tue, 29 Jul 2014 15:14:01 GMT

Well, that's a lot of separate questions there:

1) its prompting to save file.

This is likely due to the ods outputting results. Turn this off by adding the statement before your tagset one:

ods noresults;

2) its not giving desired output file.

Sorry, you will have to clarify here. The purpose of tagsets are to create markedup output, the Excel one uses XML which is a flat text file which Excel can parse and display. This is similar to the latest versions of Office which use compressed folders with XML files within.

3) it not proving suporting version of excel file

What version of Excel are we talking about? Most versions should be able to to parse XML.

4) and main issue is , its doing strange behaviour. providing missing missing value of computed variable for particular record for date 01jan2014.

Please provide specific test data/example output of what is happening. Excel does a great job of hiding most things behind the scenes, so it could be A) you options missing is setup wrong, B) Excel reads a value it doesn't like and changes it, C) the displayed data doesn't fit with general format hence its hidden etc.

It may be as simple as setting the style to set the excel format for that cell.

Re: Quote is macro variable

Patrick — Tue, 29 Jul 2014 23:06:26 GMT

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?

Re: Quote is macro variable

Aman4SAS — Wed, 30 Jul 2014 10:09:19 GMT

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;

Re: Quote is macro variable

Aman4SAS — Wed, 30 Jul 2014 10:42:06 GMT

Hi,

i have attached sample data and code , where i getting missing values for particular date july 14,

at same time when i use HTML body, its not giving any missing values .

Kindly suggest.

Re: Quote is macro variable

RW9 — Wed, 30 Jul 2014 11:01:38 GMT

Hi,

Well, have tried running your programs with the data, however they do not work:

WARNING: In Event: style_over_ride, Invalid Expression: eval $style_list[$cell_class ] $style_list[$cell_class ] +1

ERROR: WHERE clause operator requires numeric variables.

What I would suggest you do is investigate the Excel output. If as you say the HTML output has the values, and outrept=...; (i.e. save the output dataset) and check the output dataset looks correct, then It would appear to be an excel format issue.

Re: Quote is macro variable

Aman4SAS — Wed, 30 Jul 2014 11:24:47 GMT

I am worried that why error coming , i havent use where clause in my code anywhere,

please recheck my code.

and about out output i have checked in many place rtf, pdf, and html body where i m getting value but problem is i will not be able to get file with multiple sheet.

about second code i m failed to get my desired output format as i have attached, please suggest

Re: Quote is macro variable

RW9 — Wed, 30 Jul 2014 12:52:32 GMT

Well, if you are sure the data is correct going out to whichever report format then try right clicking on the XLS file generated and open in Notepad. You will see the marked up text file. Find the value that you see as missing, if it is missing there, but not in other outputs then it may be that you need to put Excel type formats on the data, so something along the lines of: style(column)={tagattr='format:text'};

Otherwise its probably best to pop a ticket in with the support desk as to why your data is not coming out the same across output formats.

With regards to your output format its really a matter of fiddling about with it until you get it how you want, although remember there are limitations to any output and trying to be 100% accurate is sometimes not practical.

Re: Quote is macro variable

Aman4SAS — Wed, 30 Jul 2014 15:16:21 GMT

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