topic Re: Tricky query in SAS Programming

Tricky query

mjack — Tue, 23 Jul 2013 08:01:10 GMT

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!

Re: Tricky query

Ksharp — Tue, 23 Jul 2013 08:48:38 GMT

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

Re: Tricky query

DJChavda — Tue, 23 Jul 2013 08:58:56 GMT

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;

Re: Tricky query

mjack — Tue, 23 Jul 2013 08:59:47 GMT

Ksharp, u r great! it works ! Thanks!

Re: Tricky query

Patrick — Tue, 23 Jul 2013 10:05:17 GMT

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.

Re: Tricky query

Haikuo — Tue, 23 Jul 2013 13:49:33 GMT

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.