BookmarkSubscribeRSS Feed
mjack
Calcite | Level 5

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!

5 REPLIES 5
Ksharp
Super User

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
Calcite | Level 5

Ksharp, u r great! it works ! Thanks!

Patrick
Opal | Level 21

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
Onyx | Level 15

,

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
Obsidian | Level 7

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;

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1034 views
  • 2 likes
  • 5 in conversation