dynamically create a folder and a file

dynamically create a folder and a file

Post by Samdoll Tr » Sat, 12 Jan 2002 03:06:38



... ...
(codes here)
... ...

(when sas executes arriving this point, a macro variable FOLDERNAME will be
generated, and will be assigned a value, say, FY01SALE.)

my question is, at this point, how to, within sas, create a folder under
D:\FYRUN. the folder name is whatever the value of the macro variable
FOLDERNAME is, which in this case is FY01SALE. under the newly generated
folder, i also need to, again within sas, create a excel file. the file name
is again whatever the value of the macro variable FOLDERNAME is, which in
this case is FY01SALE.

anybody take this one?

_________________________________________________________________
Join the worlds largest e-mail service with MSN Hotmail.
http://www.hotmail.com

 
 
 

dynamically create a folder and a file

Post by Terjeson, Ma » Sat, 12 Jan 2002 03:38:23


Hi,

There are a couple different conmands to invoke
an operating system command to create a new
directory.  If you have spaces in the path or filename
you will have to handle the quoting a bit more or
pre-concatenate the string or both.......

    * macro variable loaded by whatever means..... ;
%let FOLDERNAME=FY01SALE;

    * The X command invokes an  ;
    * operating system command. ;
    * if you do not have spaces ;
    * in the path or filename.. ;
options noxwait xsync;
x "mkdir d:\fyrun\&FOLDERNAME";
    * of course this is a PC command ;
    * you would have to taylor it to ;
    * whatever environment you use.  ;

Hope this is helpful,
Mark Terjeson
Washington State Department of Social and Health Services
Division of Research and Data Analysis (RDA)

-----Original Message-----

Sent: Thursday, January 10, 2002 10:07 AM

Subject: dynamically create a folder and a file

... ...
(codes here)
... ...

(when sas executes arriving this point, a macro variable FOLDERNAME will be
generated, and will be assigned a value, say, FY01SALE.)

my question is, at this point, how to, within sas, create a folder under
D:\FYRUN. the folder name is whatever the value of the macro variable
FOLDERNAME is, which in this case is FY01SALE. under the newly generated
folder, i also need to, again within sas, create a excel file. the file name
is again whatever the value of the macro variable FOLDERNAME is, which in
this case is FY01SALE.

anybody take this one?

_________________________________________________________________
Join the world's largest e-mail service with MSN Hotmail.
http://www.hotmail.com


 
 
 

dynamically create a folder and a file

Post by Samdoll Tr » Sat, 12 Jan 2002 03:47:42


hi, mark, thank you for the helpful answer. so the following create a folder
for me within sas:

options noxwait xsync;
x "mkdir d:\fyrun\&FOLDERNAME";

but what about creating an excel file under that newly generated folder?

-----Original Message-----

Sent: Thursday, January 10, 2002 1:38 PM

Subject: Re: dynamically create a folder and a file

Hi,

There are a couple different conmands to invoke
an operating system command to create a new
directory.  If you have spaces in the path or filename
you will have to handle the quoting a bit more or
pre-concatenate the string or both.......

    * macro variable loaded by whatever means..... ;
%let FOLDERNAME=FY01SALE;

    * The X command invokes an  ;
    * operating system command. ;
    * if you do not have spaces ;
    * in the path or filename.. ;
options noxwait xsync;
x "mkdir d:\fyrun\&FOLDERNAME";
    * of course this is a PC command ;
    * you would have to taylor it to ;
    * whatever environment you use.  ;

Hope this is helpful,
Mark Terjeson
Washington State Department of Social and Health Services
Division of Research and Data Analysis (RDA)

_________________________________________________________________
Send and receive Hotmail on your mobile device: http://mobile.msn.com

 
 
 

dynamically create a folder and a file

Post by Samdoll Tr » Sat, 12 Jan 2002 04:07:02


I think I am talking about having SAS create xls files there with X
statement or DDE. I know how to use DDE to programtically open an existing
xls files, but am not so sure how to create a new one.
-----Original Message-----

Sent: Thursday, January 10, 2002 1:54 PM

Subject: FW: dynamically create a folder and a file

Are you talking about Excel creating files there or
having SAS create Excel files there or ???

If SAS, which method?    Export  or using DDE
within the datastep or ???

PS:  if you would ever have spaces in the pathname
or the filenames most any one of us here on sas-l
can give you an adjusted syntax for handling the
quoting on that.

_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com

 
 
 

dynamically create a folder and a file

Post by Terjeson, Ma » Sat, 12 Jan 2002 05:02:08


<you wrote:>

I> think I am talking about having SAS create xls files there with X

>statement or DDE. I know how to use DDE to programtically open an existing
>xls files, but am not so sure how to create a new one.

Here is a working sample of some chunks I yanked
out of some old code.  You should be able to play
with some of this to get familiar and tinker more.....
(This is for PC, I was using NT)
(you may have to tweak the path to your excel.exe)

     * initialize parameters for dataset to be read ;
%macro dsparams(dsname);

  %global rows
          cols
          begrow
          begcol
          endrow
          endcol
          putlist;

   ***> PROC CONTENTS scans the dataset and keeps the following
        variables (there will be one observation for each variable
        in the incoming file):
               NAME   - the variable names
               VARNUM - the position of the variable in the data vector
               NOBS   - the number of observations in the dataset
                        (this is the same on each observation);

proc contents data=&dsname noprint out=stuff(keep=nobs varnum name);
run;

   ***> take the information from the PROC CONTENTS and create two
        macro variables:
               &ROWS - the number of observations in the incoming dataset
               &COLS - the number of variables in the incoming dataset
        these will be used to tell EXCEL how many rows and columns the
        spreadsheet will have;

data _null_;
  set stuff nobs=numcols;
  if begrow eq . then begrow = 1;
  if begcol eq . then begcol = 1;
  call symput('rows',trim(left(put(nobs,4.))));
  call symput('cols',trim(left(put(numcols,4.))));
  call symput('begrow',trim(left(put(begrow,4.))));
  call symput('begcol',trim(left(put(begcol,4.))));
  call symput('endrow',trim(left(put(begrow+nobs-1,4.))));
  call symput('endcol',trim(left(put(begcol+numcols-1,4.))));
  stop;
run;

%put AAA rows = &rows;
%put AAA cols = &cols;
%put AAA begrow = &begrow;
%put AAA begcol = &begcol;
%put AAA endrow = &endrow;
%put AAA endcol = &endcol;

proc sort data=stuff;
  by varnum;
run;

proc sql noprint;
    select name into :putlist separated by '09'x
    from stuff
    ;
quit;

%put CCC putlist=&putlist;

%mend;

%macro fmt_put(row,col,align,size,bold,ecol);
    %if &align eq %then %let align=L;
    %if %upcase(&align) eq L %then %let align=2;
    %if %upcase(&align) eq C %then %let align=3;
    %if %upcase(&align) eq R %then %let align=4;
    %if %upcase(&align) eq A %then
        %do;
            %let align=7;   * center across from col to ecol ;
        %end;
    %else
        %do;
            %let ecol=&col; * default to one cell range ;
        %end;
    %if &size eq %then %let size=10;
    %if &bold eq B %then %let bold=true; %else %let bold=false;
    sss = compress('[select("R'||put(&row,2.)||'C'||put(&col,2.)||':'||
                            'R'||put(&row,2.)||'C'||put(&ecol,2.)||'")]');
    put sss;
    sss = compress('[format.font("Arial",'||"&size"||','||"&bold"||')]');
    put sss;
    sss = compress('[alignment('||"&align"||')]');
    put sss;
%mend;

%macro fmt_mos(row,col);
    sss = compress('[select("R'||put(&row,2.)||"C"||put(&col,2.)||'")]');
    put sss;
    sss = compress('[format.number("mmm-yy")]');
    put sss;
%mend;

%macro fmt_cma(row,col);
    sss = compress('[select("R'||put(&row,2.)||"C"||put(&col,2.)||'")]');
    put sss;
    sss = '[format.number("#,##0; (#,##0)")]';
    put sss;
%mend;

%macro n_put(numvar,row,col,align,size,bold,ecol);
    sss = compress('[formula('||put(&numvar,11.)||
                   ',"R'||put(&row,2.)||"C"||put(&col,2.)||'")]');
    put sss;
    %fmt_put(&row,&col,&align,&size,&bold,&ecol);
%mend;

%macro c_put(strvar,row,col,align,size,bold,ecol);
    sss = '[formula("'||%quote(trim(&strvar))||
          '","R'||compress(put(&row,2.)||"C"||put(&col,2.))||'")]';
    put sss;
    %fmt_put(&row,&col,&align,&size,&bold,&ecol);
%mend;

* test data ;
data sample;
    id=1;name='Fred';amt=160;output;
    id=2;name='Barney';amt=140;output;
    id=3;name='Wilma';amt=230;output;
    id=4;name='Betty';amt=240;output;
    id=5;name='Dino';amt=110;output;
    id=6;name='BamBam';amt=60;output;
    id=7;name='Pebbles';amt=45;output;
    id=8;name='Mr. Slate';amt=33;output;
run;

%dsparams(sample);

    * macro variable loaded by whatever means..... ;
%let FOLDERNAME=FY01SALE;

    * The X command invokes an  ;
    * operating system command. ;
    * if you do not have spaces ;
    * in the path or filename.. ;
options noxwait xsync;
x "mkdir d:\fyrun\&FOLDERNAME";
    * of course this is a PC command ;
    * you would have to taylor it to ;
    * whatever environment you use.  ;

%let workbook=d:\fyrun\&FOLDERNAME\mybook.xls;

options noxwait noxsync;

*x 'c:\msoffice\excel\excel.exe'; * Win95 ;
x '"C:\Program Files\Microsoft Office\Office\EXCEL.EXE"'; * NT ;

    * wait for app to open ;
data _null_;
    x=sleep(5);
run;

   ***> set up the DDE triplet to grab the appropriate spreadsheet and
position;
filename exceldat dde "excel | &workbook !
r&begrow.c&begcol:r&endrow.c&endcol" notab;
filename excelcmd dde 'excel | system';

   ***> for some reason DDE links to commands and data cannot be used
        in the same datastep, so split the work into three steps:
            1) open a new worksheet and save it out as the selected sheet
            2) copy the dataset information to the sheet
            3) save the sheet;

  * ------- CREATE NEW WORKSHEET ------- ;
  data _null_;
    file excelcmd;
    put '[error(false)]';
    put '[new(1)]';
    put '[error(true)]';
  run;

  * ------- SAVEAS NEW FILE ------- ;
  data _null_;
    length sss  $100;
    file excelcmd;
    put '[error(false)]';
    sss = '[save.as("' || "&workbook" || '",1)]';
    put sss;
    put '[error(true)]';
  run;

   /*
  * ------- INSURE ACTIVATION OF DESIRED SHEET ------- ;
  data _null_;
    length sss  $100;
    file excelcmd;
    sss = '[activate("' || "&workbook" || ':1")]';
    put sss;
  run;

   * ------- (run an excel macro) ------- ;
  data _null_;
    length sss  $100
           stmp $100
           ref   $10
           ;
    file excelcmd;

    sss = '[run("testm3.xls!Macro1")]';
    put sss;

  run;

     */

  * ------- BODY ------- ;
data _null_;
    length sss      $100
           ;
    file excelcmd;
    set sample end=done;

    if _n_ eq 1 then
        do;
                * Page Banner ;
            %c_put("Folks from Bedrock",1,1,A,12,B,3);

                * Column Headings ;
            %c_put("ID",3,1,C,11,B);
            %c_put("Name",3,2,C,11,B);
            %c_put("Amt",3,3,C,11,B);
        end;

        * output each data row ;
    row = _n_ + 3;
    %n_put(ID,row,1,L);
    %c_put(name,row,2,L);
    %n_put(amt,row,3,L);

run;

  * ------- SAVEAS/CLOSE/EXIT FILE ------- ;
data _null_;
    length sss $100;
    file excelcmd;
    put '[error(false)]';
    sss = '[save.as("' || "&workbook" || '",1)]';
    put sss;
    put '[error(true)]';
    put '[close()]';
    put '[quit()]';
run;

Hope this is helpful,
Mark Terjeson
Washington State Department of Social and Health Services
Division of Research and Data Analysis (RDA)
mailto:terj...@dshs.wa.gov

-----Original Message-----
From: Samdoll Trek [mailto:sd_...@hotmail.com]
Sent: Thursday, January 10, 2002 11:07 AM
To: Terj...@dshs.wa.gov

Cc: SA...@listserv.uga.edu
Subject: RE: dynamically create a folder and a file

I think I am talking about having SAS create xls files there with X
statement or DDE. I know how to use DDE to programtically open an existing
xls files, but am not so sure how to create a new one.

-----Original Message-----
From: Terjeson, Mark [mailto:Terj...@dshs.wa.gov]
Sent: Thursday, January 10, 2002 1:54 PM
To: SA...@listserv.uga.edu
Subject: FW: dynamically create a folder and a file

Are you talking about Excel creating files there or
having SAS create Excel files there or ???

If SAS, which method?    Export  or using DDE
within the datastep or ???

PS:  if you would ever have spaces in the pathname
or the filenames most any one of us here on sas-l
can give you an adjusted syntax for handling the
quoting on that.

_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com

 
 
 

dynamically create a folder and a file

Post by Smith, Curtis, Mr, DC » Sat, 12 Jan 2002 05:07:30


Take a look at Koen Vyverman's SUGI 26 paper.

http://www2.sas.com/proceedings/sugi26/proceed.pdf

He also provides a fantastic macro utility that he creates called "sastoxl".
You pass it the SAS library name, SAS data set name, destination folder, and
destination xls filename. The macro does the rest.

-----Original Message-----

Sent: Thursday, January 10, 2002 11:07 AM

Subject: Re: dynamically create a folder and a file

I think I am talking about having SAS create xls files there with X
statement or DDE. I know how to use DDE to programtically open an existing
xls files, but am not so sure how to create a new one.

-----Original Message-----

Sent: Thursday, January 10, 2002 1:54 PM

Subject: FW: dynamically create a folder and a file

Are you talking about Excel creating files there or
having SAS create Excel files there or ???

If SAS, which method?    Export  or using DDE
within the datastep or ???

PS:  if you would ever have spaces in the pathname
or the filenames most any one of us here on sas-l
can give you an adjusted syntax for handling the
quoting on that.

_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com

 
 
 

1. FW: dynamically create a folder and a file

Are you talking about Excel creating files there or
having SAS create Excel files there or ???

If SAS, which method?    Export  or using DDE
within the datastep or ???

PS:  if you would ever have spaces in the pathname
or the filenames most any one of us here on sas-l
can give you an adjusted syntax for handling the
quoting on that.

2. GTA3 info

3. How to create a folder with the name "New Folder (x)"

4. Efficient Line Clearing

5. Creating zip file dynamically

6. Config Mgmt with SourceSafe

7. print dynamically a text from the pdf file and hide text dynamically

8. Mixing drives with different speeds

9. Dynamically created PDF files

10. How to create a PDF bookmarking all the PDF files of a folder ?

11. Help me ... i m looking for an OCX to cpompress folder / sub folders and files ...

12. Moving files from folder to folder