PROC SQL and MACROS ???!!!!!

[Kirito1]

So I was working on SAS and I was using proc SQL, the code provided below.

PROC SQL;
CREATE TABLE TO2A AS
SELECT DISTINCT(FIN_EMP_CODE), COUNT(count) AS 'today2'n
FROM to2 
GROUP BY FIN_EMP_CODE;
QUIT;

But, I want to name the column name as today()-2 means the column name should be named 16/01/2023, something like that instead of today2.

I have a fade idea of how to do it like we can use macros but I think we can use macros within proc sql. What should I do, what should be my approach? Can anyone guide......

Thanks

[Kirito1]

Can someone help 😞

[yabwon]

What you are asking can be donr like that:

/* set the value */
%let today2=%sysfunc(intnx(day,%sysfunc(today()),-2,s), ddmmyy10.);
/* display to check if is ok*/
%put &=today2.;


/* option for '...'n in variables names */
options validvarname=any; 

PROC SQL;
CREATE TABLE test AS
SELECT origin, sum(invoice) AS "&today2."n
FROM sashelp.cars
GROUP BY origin;
QUIT;

proc print data=test;
run;

But! I wouldn't do that.

Instead setting a variable name to '16/01/2023'n I would use label statement,and kept variable name something like 'date2' or 'today_2' etc.

PROC SQL;
CREATE TABLE test2 AS
SELECT origin, sum(invoice) AS date2 label "Value from &today2."
FROM sashelp.cars
GROUP BY origin;
QUIT;

proc print data=test2 label;
run;

proc print data=test2 ;
run;

With this approach you:

1) don't have to worry about dataset portability (validvarname=and doesn't have to be everywhere on)

2) for prints out you can always use 'label' to make it look good

3) it's easier call to value 'date2' in other parts of the code (using macrovariables) rather to " '16/01/2023'n " for examlpe you can do something like this:

/**/
%let history=2;
/* set the value */
%let today&history.=%sysfunc(intnx(day,%sysfunc(today()),-&history.,s), ddmmyy10.);
/* display to check if is ok*/
%put value: &&today&history.;

and easily modify how far into the past you want to look

All the best

Bart

[Kirito1]

yup I was worrying about the same but your solution has made it clear.
Thanks Bart 🙂

[Kurt_Bremser]

It is usually a BAD idea to have data (dates) in structure (variable names), also see Maxim 19.

Please explain why you think you need a column with a date as name.

[PaigeMiller]

@Kirito1 

I agree with the others, DO NOT name a variable with a calendar date. This is a bad idea for many reasons, not just the reasons mentioned. Please explain what you are trying to do, and why a name like DATE isn't good enough.

[Tom · Accepted solution]

To use a macro variable to help generate code first figure out what code you want.

First clean up you SQL so it looks understandable.  (Note DISTINCT is a KEYWORD and not a function and you don't need it if you are already grouping).

create table to2a as
  select fin_emp_code, count(count) as "2023/01/16"n
  from to2
  group by fin_emp_code
;

Now replace the variable part with a reference to a macro variable.

create table to2a as
  select fin_emp_code, count(count) as &name.
  from to2
  group by fin_emp_code
;

So now you are ready to figure out how to create the NAME macro variable.

You can use %SYSFUNC() to call SAS functions in macro code. 

%let name="%sysfunc(putn(%sysfunc(today())-2,yymmdd10.))"n;
create table to2a as
  select fin_emp_code, count(count) as &name.
  from to2
  group by fin_emp_code
;

A couple of notes.

One is don't display dates in either MDY or DMY order. Either choice will confuse 50% of your audience.

And do not store data, like a date value, into metadata, like a variable name.

Why not just make a dataset like this instead?

create table to2a as
  select fin_emp_code, today()-2 as report_date format=yymmdd10.,count(count) as n_cases
  from to2
  group by fin_emp_code
;