Help using Base SAS procedures

Pulling data from SQL serever for a particular month

Reply
Occasional Contributor
Posts: 17

Pulling data from SQL serever for a particular month

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;

%end;

%mend;

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

 

Contributor
Posts: 34

Re: Pulling data from SQL serever for a particular month

Posted in reply to tampham92

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;

Super User
Posts: 5,884

Re: Pulling data from SQL serever for a particular month

Posted in reply to tampham92
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..
Data never sleeps
Ask a Question
Discussion stats
  • 2 replies
  • 134 views
  • 0 likes
  • 3 in conversation