topic Re: Macro help in SAS Programming

Macro help

Abraham — Mon, 07 Jul 2014 12:11:02 GMT

Hello Everyone!

I want to print different excel file on particular location based on the name from dataset 'Country'.(Program in attached text file)

To that file, I want to add another excel file output in sheet2 which are generated from another table "XXX" ( if the country are matching)

But I am not able to succeed.

Can anybody help me.

Attached program and output to be displayed for your reference.

May be is there any altenative way to keep the code simple

Thanks in advance

Re: Macro help

RW9 — Mon, 07 Jul 2014 12:40:15 GMT

Not sure I am following what the problem is. You could change the code slightly to:

proc sql;

create table LOOP as

select distinct COUNTRY from HAVE;

quit;

ods tagsets.excelxp file="...\file.xlsx";

/* This loops over each country and prints the dataset with the corresponding country name to the tagset */

data _null_;

set LOOP;

call execute('ods tagsets.excelxp options=(sheet_name="'||strip(country)||'");

proc print data='||strip(country)||';

run;');

run;

ods tagsets.excelxp options=(sheet_name="Help");

...

ods tagsets.excelxp close;

Re: Macro help

Abraham — Tue, 08 Jul 2014 08:13:57 GMT

Thanks RW for looking into the issue.

I have not that much idea regarding the macro.

In my attached program, three datasets are there (country, xxx and cre_time) out of which I want to print the excel file based on distinct name from dataset country. (e.g. if name =india, US, then two excel output will be generated ).

Once the excel file is generated, I want to create another sheet (on the existing) name as "Data2". To the sheet (Data2), data will be added from another dataset (xxx) if the name are matching.

e.g. For datasets xxx and country, the common matching variable is 'name'. So data from dataset (xxx) will be added to sheet (Data2).

I attach output for your reference.

Output should be like below (as distinct name would be india, us and uk, so three excel spreadsheet will be created in C drive)

UK_23JAN2014_25MAR2014.xlsx

US_23JAN2014_25MAR2014.xlsx

India_23JAN2014_25MAR2014.xlsx

Re: Macro help

RW9 — Tue, 08 Jul 2014 08:43:15 GMT

Hi,

Yes, you just need to modify the code presented:

proc sql; /* Get a list of countries, we will loop over the code once for each of them */

create table LOOP as

select distinct COUNTRY from HAVE;

create table DATA2 /* Create an empty table to print to data2 */

(COL1 char(200));

quit;

/* This loops over each country and prints the dataset with the corresponding country name to the tagset */

data _null_;

set LOOP;

call execute('ods tagsets.excelxp file="...\'||strip(country)||'.xlsx"; /* Create a file as per country */

ods tagsets.excelxp options=(sheet_name="'||strip(country)||'"); /* Set the sheetname as country */

proc print data=xxx where=(country="'||strip(country)||'")); /* Print the xxx data only where country=current country */

run;');

call execute('ods tagsets.excelxp options=(sheet_name="data2");

proc print data=data2; /* Print the empty dataset data2 to the spreadsheet to get second tab */

run;

ods tagsets.excelxp close;');

run;

Re: Macro help

Abraham — Tue, 08 Jul 2014 10:03:42 GMT

Thank You RW9 for explaining elaborately. I have modified the code, excel file also generated but with no output. I have also added the time range. May I request you to have a look once again Thanks in advance

Re: Macro help

RW9 — Tue, 08 Jul 2014 13:06:11 GMT

Sorry, my post got removed for some reason. See attached, note that name needs to be in xxx exactly, you had some extra characters.

Re: Macro help

bentleyj1 — Tue, 08 Jul 2014 14:57:44 GMT

Nice solution RW9. I never think to use call execute() for something like this. I would have put the country values into macro variables and then used a macro with a %do loop.

Re: Macro help

Tom — Tue, 08 Jul 2014 16:03:29 GMT

Personally I find it much easier to generate the code using a data step and then %INCLUDE it than use CALL EXECUTE.

1) You can look at the generated code and check if it is right. Pull it into the editor and submit the top one or two blocks.

2) You can take advantage of the power of the PUT command and avoid string concatenation. For example if you dataset variable names match your macro parameter names or statement option names then you can use the VAR= syntax in the PUT statements to get both the name and the value output.

3) You can avoid the timing issues of having macro statements executed while the commands are bring pushed onto the stack.

4) You can avoid generating hundreds (thousands) of macro variables and having to create macro to allow %DO to loop over them.

filename code temp;

data _null_;

set LOOP;

file code ;

file=cats('...\',country,'.xlsx');

put 'ods tagsets.excelxp ' file= :$quote. ';' ;

put 'ods tagsets.excelxp options=(sheet_name=' country :$quote. ');' ;

put 'proc print data=xxx;'

/ ' where ' country= :$quote. ';'

/ 'run;'

;

put 'ods tagsets.excelxp options=(sheet_name="data2");' ;

put 'proc print data=data2;'

/ 'run;'

;

put 'ods tagsets.excelxp close;' ;

run;

%include code / source2 ;

Re: Macro help

RW9 — Tue, 08 Jul 2014 16:25:40 GMT

Yes, I have seen this approach a fair bit over time. Personally I don't like the temporary files lying around (which also show up in version control), and there is a minor overhead of read/write to the file. If you don't trust your code or want to run parts of it then just add a debug in (and maybe keep the dataset in work:

data stored_code;

set loop;

attrib cmdline format=$2000.;

/* Create your commands and output */

cmdline=cats'ods tagsets.excelxp file="',,,,, ; output;

cmdline='proc print...;

/* Run only commands up to a certain point */

if 1 <= _n_ <= /* Enter a value here of command lines to process */

call execute(cmdlien);

end;

run;

Re: Macro help

Tom — Tue, 08 Jul 2014 17:14:21 GMT

SAS deletes the temporary files when you clear the fileref or close the session.

Re: Macro help

Abraham — Wed, 09 Jul 2014 03:47:52 GMT

Thank You so much RW9 for providing the solution. Thank you TOM also.