BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Gil_
Quartz | Level 8
Hi I need to filter a datetime25.6
I have a libname
Libname ecdb odbc dsn= ec user= me password=@@@ schema=ec;

Data_null_;
Format c date7. e date7.;
C= datepart (datwtime ();
E=c+7;
Call symput ('cdate2', " ' " | |vvalue (e) | |" 'd");
Call symput ('cdate', " ' " | |vvalue (c) | |" 'd");

%put &cdate2. ;
%put &cdate.;
My where statement
WHERE AL1.trans datepart (AL1,TRANS)>&CDATE2 AND DATEPART (AL1.TRANS)=<&CDATE;


It does id the date but it doesn't complete the query it just says proc sql running in my log it shows macro variable cdate2 resolves to '18Aug17' d
variable cdate resolves to '11Aug17' d

Thanks for assistance
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

You should make it a point to post syntactically valid code.

Your data step produces this log:

24         Data_null_;
           __________
           180

ERROR 180-322: Statement is not valid or it is used out of proper order.

25         Format c date7. e date7.;
           ______
           180

ERROR 180-322: Statement is not valid or it is used out of proper order.

26         C= datepart (datwtime ();
           _
           180

ERROR 180-322: Statement is not valid or it is used out of proper order.

27         E=c+7;
           _
           180

ERROR 180-322: Statement is not valid or it is used out of proper order.

28         Call symput ('cdate2', " ' " | |vvalue (e) | |" 'd");
           ____
           180

ERROR 180-322: Statement is not valid or it is used out of proper order.

29         Call symput ('cdate', " ' " | |vvalue (c) | |" 'd");
           ____
           180

2                                                          Das SAS System                              08:44 Friday, August 11, 2017

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent symbolic reference CDATE2 not resolved.
30         
31         %put &cdate2. ;
&cdate2.
32         %put &cdate.;
WARNING: Apparent symbolic reference CDATE not resolved.
&cdate.

So the people that are supposed to help you will first have to fix that heap of bugs.

Note that posting code into the main posting window will reformat, drop blanks, replace certain character combinations with smileys, etc.

Use the {i} or "little running man" buttons for posting code!

(I've probably written that a thousand times now, memo to self: create keyboard macro)

 

A condition like

WHERE AL1.trans datepart (AL1,TRANS)>&CDATE2 AND DATEPART (AL1.TRANS)=<&CDATE;

wont't work in a data step or proc sql.

 

Please post the code you really ran. You just need to copy/paste that working code into one of the windows mentioned above.

And post the attributes of the variable al1.trans (type, length, format).

View solution in original post

3 REPLIES 3
Shmuel
Garnet | Level 18

Is that all your code ?

If YES than you have mixed data step code with sql code.

if NOT please post your full code or log.

 

Anyhow, your where staement relates only to the DATEPART and not to the time.

Please explain your issue with an example - what havr you expected and what have you got .

Kurt_Bremser
Super User

You should make it a point to post syntactically valid code.

Your data step produces this log:

24         Data_null_;
           __________
           180

ERROR 180-322: Statement is not valid or it is used out of proper order.

25         Format c date7. e date7.;
           ______
           180

ERROR 180-322: Statement is not valid or it is used out of proper order.

26         C= datepart (datwtime ();
           _
           180

ERROR 180-322: Statement is not valid or it is used out of proper order.

27         E=c+7;
           _
           180

ERROR 180-322: Statement is not valid or it is used out of proper order.

28         Call symput ('cdate2', " ' " | |vvalue (e) | |" 'd");
           ____
           180

ERROR 180-322: Statement is not valid or it is used out of proper order.

29         Call symput ('cdate', " ' " | |vvalue (c) | |" 'd");
           ____
           180

2                                                          Das SAS System                              08:44 Friday, August 11, 2017

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent symbolic reference CDATE2 not resolved.
30         
31         %put &cdate2. ;
&cdate2.
32         %put &cdate.;
WARNING: Apparent symbolic reference CDATE not resolved.
&cdate.

So the people that are supposed to help you will first have to fix that heap of bugs.

Note that posting code into the main posting window will reformat, drop blanks, replace certain character combinations with smileys, etc.

Use the {i} or "little running man" buttons for posting code!

(I've probably written that a thousand times now, memo to self: create keyboard macro)

 

A condition like

WHERE AL1.trans datepart (AL1,TRANS)>&CDATE2 AND DATEPART (AL1.TRANS)=<&CDATE;

wont't work in a data step or proc sql.

 

Please post the code you really ran. You just need to copy/paste that working code into one of the windows mentioned above.

And post the attributes of the variable al1.trans (type, length, format).

Gil_
Quartz | Level 8
Hi thanks for the assistance I fig out the issue I was creating a proc sql table with connection to my odbc... o.n my from statement I refer the lib ..obce I corrected it worked an pull in the date range

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 2712 views
  • 1 like
  • 3 in conversation