topic Re: extracting month wise data in SAS Procedures

extracting month wise data

deleted_user — Tue, 12 Jan 2010 00:03:02 GMT

Hi Friends

How do i extract month wise data from a table. Here's my code:

proc sql;

%Let Load_date = '01NOV2009'd;

Select DATEPART(PARAM_DATE_LOW)
into :Load_date
From param_table
Where PARAM_ID = 'TABLE1';

/*Store Source Record count to load*/

Select count(*) into :src_rec_count
From source_table src
Where data_column = &Load_date

quit;

let's say PARAM_DATE_LOW has vlaue 01-Nov-09. In this 2nd select statement, I want only november month's data from source_table.

How can I achieve that?

Thanks

Re: extracting month wise data

sbb — Tue, 12 Jan 2010 00:54:54 GMT

You can use the INTNX function in your SELECT statement along with your constant value of the month-start-date. The INTNX function can decrement/increment different SAS variable values - check the DOC for details.

Scott Barry
SBBWorks, Inc.

SAS Language Reference: Dictionary, INTNX Function

Increments a date, time, or datetime value by a given time interval, and returns a date, time, or datetime value.
http://support.sas.com/documentation/cdl/en/lrdict/62618/HTML/default/a000212700.htm

Suggested Google advanced search argument:

proc sql select date intnx site:sas.com

Re: extracting month wise data

DanielSantos — Tue, 12 Jan 2010 09:19:48 GMT

If I got this right, you want to extract data from some table based on a specified month which is extracted from a parameter table. Well, you have at least two ways of doing this, both of them based on matching transformed dates.

The simple one would be to match numeric month/year:
[pre]
%Let Load_date = '01NOV2009'd;

proc sql;
select datepart(PARAM_DATE_LOW)
into :LOAD_DATE
from param_table
where PARAM_ID = 'TABLE1';

select count(*) into :SRC_REC_COUNT from SOURCE_TABLE_SRC
where month(data_column) = month(&LOAD_DATE) and
year(data_column) = year(&LOAD_DATE);
quit;
[/pre]

The other one would be to match alphas with the formatted date:
[pre]
%Let Load_date = '01NOV2009'd;

proc sql;
select datepart(PARAM_DATE_LOW)
into :LOAD_DATE
from param_table
where PARAM_ID = 'TABLE1';

select count(*) into :SRC_REC_COUNT from SOURCE_TABLE_SRC
where strip(put(DATA_COLUM,yymon6.))=
(select strip(put(datepart(PARAM_DATE_LOW),yymon6.))
from param_table
where PARAM_ID = 'TABLE1');
quit;
[/pre]

Code above not tested.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt