BookmarkSubscribeRSS Feed
tampham92
Obsidian | Level 7

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);

 

2 REPLIES 2
morgalr
Obsidian | Level 7

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;

LinusH
Tourmaline | Level 20
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

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1300 views
  • 0 likes
  • 3 in conversation