topic Query builder in sas in SAS Programming

Query builder in sas

Pandu2 — Thu, 10 Mar 2022 14:56:21 GMT

Hi All,
I Hope you all doing Great.
I require some assistance from you on how to use today's date in filter data tab in query builder. Any help would be greatly appreciated. Thanks in advance.

Re: Query builder in sas

ChrisNZ — Thu, 10 Mar 2022 05:10:37 GMT

T1.date is not a SAS date it seems? Why? You are making your life harder if you don't store a SAS date.

202210 this refers year and date

I only see year and month?

. That value needs to be changed on daily basis

Why change daily if you just filter on month?

Re: Query builder in sas

Pandu2 — Thu, 10 Mar 2022 14:55:12 GMT

It is T1.xyz it was already stored in a table so I'm just fetching the data by using the condition in filter the data tab i.e
T1.xyz = abcd it refers year and date so whenever the system date hits new week the date should change accordingly.
Thanks.

Re: Query builder in sas

ChrisNZ — Thu, 10 Mar 2022 06:17:43 GMT

I suppose you could use something like

where T1.Fiscalweek = year(today()) * 100 + week(today())

Check the week() function to see options about how the week is calculated

Re: Query builder in sas

Pandu2 — Thu, 10 Mar 2022 14:57:06 GMT

Can't be thankful for your assistance. This is working nicely but needs to ensure whether it will change the date whenever the system date hits new week.

Re: Query builder in sas

ChrisNZ — Thu, 10 Mar 2022 07:18:03 GMT

Try with a different date instead of today().

Try '14mar2022'd for example.

Did you read about the week () function to ensure you have to appropriate option in place?

Re: Query builder in sas

Pandu2 — Thu, 10 Mar 2022 14:59:02 GMT

Re: Query builder in sas

Pandu2 — Thu, 10 Mar 2022 14:57:58 GMT

Re: Query builder in sas

Kurt_Bremser — Thu, 10 Mar 2022 07:33:28 GMT

TRY IT. (Maxim 4)

Run this:

data check;
do date = '01jan2022' to today();
  week = week(date);
  output;
end;
format date yymmdd10.;
run;

and look at the dataset to see if it provides the week numbers you expect. If this is not the case, post examples and the numbers you expect instead.

Re: Query builder in sas

Kurt_Bremser — Thu, 10 Mar 2022 07:39:17 GMT

@Pandu2 wrote:
I tried by providing different dates but I found something strange. Feb 28th started on Monday and the week ended on 6th March for this entire week it should give the FW as 09 but from 28th Feb to 05Th March it is giving as 09. For 06th March it is giving the FW as 10th even though 6th was part of old week.

Study the documentation (Maxim1) of the WEEK Function . It provides an additional parameter (called "descriptor" in the doc) which controls when the week begins, and how to treat incomplete weeks at the beginning of the year.

Re: Query builder in sas

Reeza — Thu, 10 Mar 2022 16:22:23 GMT

Please do not delete the contents of your post. The purpose of the forum is to create a searchable history for others as well.

And when your questions are answered please select the appropriate response that answered your question and select that as the solution.