BookmarkSubscribeRSS Feed
Aman4SAS
Obsidian | Level 7

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;

%end;

%mend;

please suggest me . Thanks in advance.

18 REPLIES 18
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Aman4SAS
Obsidian | Level 7

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Aman4SAS
Obsidian | Level 7

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Patrick
Opal | Level 21

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?

Aman4SAS
Obsidian | Level 7

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Aman4SAS
Obsidian | Level 7

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Aman4SAS
Obsidian | Level 7

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Aman4SAS
Obsidian | Level 7

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

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