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 😞
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;
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?
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.
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()
@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);
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;
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.
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);
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.
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)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: