HELP WITH MACRO FUNCTION

[tuananhle269xx]

I try to create the function TABLE_CREATION(YYYYMM)

It means for each month, it will create one table from given dataset

%MACRO TABLE_CREATION(YYYYMM);

PROC SQL;
create table info (keep = id datadate compname sales prm fyearq) as
select a.*
from statistic a
where id in ('011636', '014489')
and intnx(month,"&YYYYMM"d,-3) <= datadate <= "&YYYYMM"d;

%MEND GET_DATA;

%GET_DATA(201612);

The red line got something when calling macro variables. Does anyone know the problem? Thank you 😞

[RW9]

There are quite a few topics on here which match your question, please try search function in future.  Date literals, which are indicated by a trailing d, can only be DDMMMYYYY format which is why its not working.  See below fixed code.  Also, why is this a macro, there is no need for it?  See second example.  Just to note, I removed the keep and replace the variables in the select - if you want to use SQL best to use SQL syntax and avoid putting datastep things in it otherwise the SQL is not portable.  Out of interest why not just use base SAS per example 3?

Example 1 - fixing the code given - note there were quite a few issues (macro not called the same is one):

%macro table_creation (d=); 
  proc sql;
    create table INFO as
    select  A.ID,
            A.DATADATE,
            A.COMPNAME,
            A.SALES,
            A.PRM,
            A.FYEARQ
    from    STATISTIC A
    where   ID in ('011636', '014489')
      and   intnx(month,"&d."d,-3) <= datadate <= "&d."d;
  quit;
%mend table_creation;
%table_creation (01JAN2015);

Example 2: No macro, just a %let statement to change:

%let d=01JAN2015;

proc sql;
  create table INFO as
  select  A.ID,
          A.DATADATE,
          A.COMPNAME,
          A.SALES,
          A.PRM,
          A.FYEARQ
  from    STATISTIC A
  where   ID in ('011636', '014489')
    and   intnx(month,"&d."d,-3) <= datadate <= "&d."d;
quit;

Example 3 - simple datastep:

%let d=01JAN2015;

data want;
  set statistic (keep=id datadate compname sales prm fyearq);
  where id in ('011636', '014489') and intnx(month,"&d."d,-3) <= datadate <= "&d."d;
run;

[Astounding]

The idea looks good, but might need a little tweaking to get the right date range.  If you were to run the program using:

%let d=01JAN2015;

Which range of dates should be selected?  

[tuananhle269xx]

Thank you so much for your reply. 

I try to use the first code but it didn't work. SAS notifies error: More positional parameters found than defined.

libname myLib "C:\";
%macro table_creation (d=);
  rsubmit;
  libname comp '/sasdata/file';  
  proc sql;
    create table INFO as
    select  A.ID,
            A.DATADATE,
            A.COMPNAME,
            A.SALES,
            A.PRM,
            A.FYEARQ
    from    STATISTIC A
    where   ID in ('011636', '014489')
      and   intnx(month,"&d."d,-3) <= datadate <= "&d."d;
  quit;

proc download data= INFO out=myLib.example;
run;
endrsubmit;

proc export
DATA=myLib.example
OUTFILE="C:\&d.dta"
DBMS=dta REPLACE;
run;

%mend table_creation;
%table_creation (31DEC2016);

I have another small question if you don't mind. How can I create the loop to create table for each month over a particular time period (let's say 31DEC1970 to 31DEC2016) and export to the .dta file monthly (name that month and year) with SAS Code above ?

I really appreciate that.  

[Patrick]

@tuananhle269xx

It's may be better if you don't pack too much into a macro. Especially try and avoid to have any RSUBMIT blocks intermingled with macro definitions.

Here a code sampel for you to build upon it.

/* set start and end date */
%let startdt=01Oct1949;
%let enddt	=01Sep1950;

/* align dates to month begin and end date */
%let startdt=%sysfunc(intnx(month,"&startdt"d,0,b),date9.);
%let enddt=%sysfunc(intnx(month,"&enddt"d,0,e),date9.);

/* upload macro variables to remote session */
%syslput _user_;

/* download the data from remote to local session */
/*rsubmit test wait=yes ;*/
rsubmit;
	proc download 
		data=sashelp.airline (keep=date air region) 
		out=work.airline;
		where region="ALL" 
			and date between intnx('month',"&startdt"d,0,'b') and intnx('month',"&enddt"d,0,'e')
	; 
	run;
endrsubmit;

/* create monthly external files */
%macro spitItOut();
  %local month_cnt month_list;
  %let month_cnt=0;
  proc sql noprint;
    select count(*), put(month_begin,date9.)
        into :month_cnt, :month_list separated by ' '
    from
      (
        select 
          distinct (intnx('month',date,0,'b')) as month_begin 
        from work.airline
      )
    ;
  quit;

  %put &=month_list;
  %put &=month_cnt;

  %do i=1 %to &month_cnt;
    %local thisMonth thisMonthFormated;
    %let thisMonth=%scan(&month_list,&i);

    proc export
      data=work.airline(where=(intnx('month',date,0,'b')="&thisMonth"d))
      outfile="c:\temp\out_&thisMonth..dta"
      dbms=dta replace;
    run;
  %end;
%mend;
%spitItOut()

[tuananhle269xx]

@RW9 Sorry for this inconvenience. I still run into one trouble. Thank you for your help. 

libname myLib "C:\";
%macro table_creation (d=);
  rsubmit;
  libname comp '/sasdata/file';  
  proc sql;
    create table INFO as
    select  A.ID,
            A.DATADATE,
            A.COMPNAME,
            A.SALES,
            A.PRM,
            A.FYEARQ
    from    STATISTIC A
    where   ID in ('011636', '014489')
      and   intnx(month,"&d."d,-3) <= datadate <= "&d."d;
/**  WARNING: Apparent symbolic reference D not resolved.
ERROR: Invalid date/time/datetime constant "&d."d.  **/
  quit;

proc download data= INFO out=myLib.example;
run;
endrsubmit;

%mend table_creation;
%table_creation (d=31DEC2016);

[Tom]

If you want to reference a macro variable in code submitted to a remote SAS session then make sure the macro variable is defined in that session.

%macro table_creation (d=);
  %syslput d=&d;
  rsubmit;
...
 intnx(month,"&d."d,-3) <= datadate <= "&d."d 
...
endrsubmit;
%mend table_creation;

[tuananhle269xx]

It works. Thank you so much !!

[Kurt_Bremser]

Macro variable d is defined in the local macrovar table in your local SAS session. The code in the rsubmit runs in the remote environment that has no access to the macro variable table of your local session.

Look if the %syslput macro statement helps in your situation.

[tuananhle269xx]

Thank you. It works 🙂

[RW9]

Well, month should have quotes around it, that might be it, this works fine however, and I can't tell from what you have posted:

data temp;
  set sashelp.class;
  datadate='09JAN2017'd;
  format datadate date9.;
run;
%macro table_creation (d=);
  proc sql ;
    create table INFO as
    select  *
    from    TEMP
    where   SEX in ('M')
      and   intnx('month',"&d."d,-3) <= datadate <= "&d."d;
  quit;
%mend table_creation;
%table_creation (d=31DEC2016);

[RW9]

Several things, lets start by your issue:

%table_creation (d=31DEC2016);

Note that I now put d= before it.  There is two methods of parameters to macros, positional and named.   Take:
%macro test (a,b);

This is positional, a will always have the first value, b the second, this:

%macro test (a=,b=);

Now in this it doesn't matter if I put a= second or at any point, a will always be what is named.  I would advise to always use named parameters.

Now with your second point, firstly I would say that creating monthly files is not a great idea, it just means lots of files to import.  Either have cumulative or incremental files, i.e. all data each tranfer, or all data from a point each transfer both in one file.  Simple to send and simple to import.  I see this wanting to put dates into filenames a lot and all the code assiciated with it is messy and prone to failure, not to mention it doesn't add any value.  One data file with a date/time field is both easier to use and contains the same information.  If I had to do it this way, and wouldn't recommend I would do:

data _null_;
  do months=0 to 12;
    call execute('proc export data=mylib.example 
                        outfile="c:\'||strip(put(intnx('month',"&d."d,i),date9.))||'.dta"
                        dbms=dta replace;
                        where date_in_date between "&d."d and '||intnx('month',"&d.",i)||';
                      run;');
  end;
run;

This will generate a proc export code for each month between &d and &d + 12.

[Kurt_Bremser]

Learn to walk before you try to run, and only when you're an accomplished runner spread your wings and try to fly

(SAS concepts like dates - walk

SQL and advanced programming - run

macro and further advancement - fly)