BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Abraham
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

View solution in original post

10 REPLIES 10
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Abraham
Obsidian | Level 7

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Abraham
Obsidian | Level 7

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Abraham
Obsidian | Level 7

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

bentleyj1
Quartz | Level 8

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.

Tom
Super User Tom
Super User

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 ;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Tom
Super User Tom
Super User

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

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
  • 10 replies
  • 2548 views
  • 4 likes
  • 4 in conversation