Tricky query

[mjack]

Hi Experts,

There is a challenging query for me though may not be for SAS experts...

proc sql;
create table tab1 as
select objects, count(*) as xxxxxxxxxxx  from tab2
WHERE
     DATESAS >= intnx('month',today(),-1,'begin') and
     DATESAS <= intnx('month',today(),-1,'end')
group by objects;
quit;

In place of xxxxxxxxx I need  to display it as Jun_Count ( when I execute this query in July...)
and when I execute this query in August, it should display  Jul_Count  and so on...

Experts , pls help!

[Ksharp]

Should be :

%let mon=%sysfunc(intnx(month,%sysfunc(today()),-1) ,monname3.);

%put &mon ;

proc sql;

create table tab1 as

select objects, count(*) as &mon._Count from tab2

WHERE

     DATESAS >= intnx('month',today(),-1,'begin') and

     DATESAS <= intnx('month',today(),-1,'end')

group by objects;

quit;

Ksharp

[mjack]

Ksharp, u r great! it works ! Thanks!

[Patrick]

Then please mark Ksharp's answer as correct so this question appears as answered and you're also giving credit to the one who deserves it.

[Haikuo]

,

I don't know that you can omit %sysfunc(putn()), what is going on here? can you explain it a little more?

Thanks in advance,

Haikuo

NM, got my answer: SAS(R) 9.3 Macro Language: Reference

I have to remember RTFM all the time.

[DJChavda]

Hi jack

Try this

data _nall_;

x =intnx('month',today(),-1,'begin');

x1 = compress(catx('',propcase(substr(put(x,date9.),3,3)),'_','Count'));

call symput('xxxxxxxxxxx',x1);

run;

proc sql;
create table tab1 as
select objects, count(*) as &xxxxxxxxxxx  from tab2
WHERE
     DATESAS >= intnx('month',today(),-1,'begin') and
     DATESAS <= intnx('month',today(),-1,'end')
group by objects;
quit;