topic Re: Pulling data from SQL serever for a particular month in SAS Procedures

Pulling data from SQL serever for a particular month

tampham92 — Wed, 08 Nov 2017 17:33:46 GMT

Hello all,

I am trying to run this code to pull data from a sql server for 08/31/2017 month particularly. That's why I specify the WHERE statement. I looked at the Source data, the field PORTFOLIO (to specify the month) is text file in format of 083117. The &adate has the same format as 083117 but I don't why it still did not pull the data for me. I tried this

%nrbquote(')&adate%nrbquote(')and still did not work. Can someone help me please?

%macro loopthrough(from_date=, to_date=);

%local filedate;

%do filedate=&from_date %to &to_date;

%if %substr(&filedate,5,2) ge 01 AND %substr(&filedate,5,2) le 12 %then %do;

data _null_ print;

tmp = intnx('month',input("&filedate",yymmn6.),0,'e');

call symput('adate', put(tmp, mmddyy7.));

run;

%put &adate.;

PROC SQL;

CONNECT TO SQLSVR (...);

CREATE TABLE DATA_&filedate. AS SELECT * FROM CONNECTION TO sasqrm

(SELECT *

FROM

source data goes here

WHERE

PORTFOLIO = &adate.)

;

QUIT;

%end;

%mend;

%loopthrough(from_date=201708, to_date=201708);

Re: Pulling data from SQL serever for a particular month

morgalr — Thu, 09 Nov 2017 01:26:44 GMT

This SQL will pull for a specific year/month combination. I think that is what you needed, but one thing to check:

our SQL db has DateTimes and Dates, make sure of which one you are working with before using the "datepart" function.

proc sql;
create table tmp as
select myDate from myTable where put(datepart(myDate), yymmn6.)='201708';
quit;

Re: Pulling data from SQL serever for a particular month

LinusH — Thu, 09 Nov 2017 06:55:39 GMT

First, have non macro version of your code working.
Then by using MPRINT you get the same syntax with macro logic.
Then I see no reason to use explicit SQL pass through here, it just complicates the code..