Tricky query

Reply
Contributor
Posts: 42

Tricky query

Hi Experts,

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

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!

Super User
Posts: 10,023

Re: Tricky query

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

Contributor
Posts: 42

Re: Tricky query

Ksharp, u r great! it works ! Thanks!

Respected Advisor
Posts: 4,173

Re: Tricky query

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.

Respected Advisor
Posts: 3,156

Re: Tricky query

,

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.

Occasional Contributor
Posts: 13

Re: Tricky query

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;

Ask a Question
Discussion stats
  • 5 replies
  • 267 views
  • 2 likes
  • 5 in conversation