topic Re: How to timestamp a tablename with proc sql in the format of YYYYMMDDHHSS in SAS Programming

How to include a timestamp in the style YYYYMMDDHHSS in a filename

aarnold18 — Thu, 21 Mar 2024 16:00:29 GMT

Hello,

I am trying to create a table with the timestamp name Member_YYYYMMDDHHSS using proc sql.

Does anyone know how to insert that into the table name?

thank you

Re: How to timestamp a tablename with proc sql in the format of YYYYMMDDHHSS

ballardw — Thu, 21 Mar 2024 15:30:19 GMT

First, are you sure that you want to use 12 of the available 32 characters to name a SAS data set for this?

And exactly how are you envisioning "proc sql" comes into this? The syntax for naming a data set doesn't change in SQL so I wonder if you have something odd in mind.

Re: How to timestamp a tablename with proc sql in the format of YYYYMMDDHHSS

aarnold18 — Thu, 21 Mar 2024 15:33:59 GMT

Hi Ballard,

Well the table name itself doesn't have to have the timestamp. However the exported table name does.

Currently I have the date format figured out:

%let now=%sysfunc(datetime());
%let date=%sysfunc(putn(&now,B8601DN8))%sysfunc(timepart(&now),B8601TM6);

However when I use this export function, the filename gets exported as Member_&date and not with the actual digits in the name.

proc export data=Member
outfile='........../Member_&date..csv'
dbms=csv
replace;
run;

So I guess I need my proc export function adjusted

Re: How to timestamp a tablename with proc sql in the format of YYYYMMDDHHSS

Tom — Thu, 21 Mar 2024 15:42:41 GMT

I suspect you will want to also include 2 digits for the minutes.

SQL does not have anything to do with this since it just runs the code you give it. You will need to use some code generation. The macro processor is good at that.

Do you have particular timestamp you want to use? You can use the DATETIME() function to get the current clock time. Use the B8601DT format to make string of digits with the letter T between the date and the time of day parts. If you don't want the T use COMPRESS() to remove it.

%let ts=%sysfunc(compress(%sysfunc(datetime(),b8601dt15.),T));

data want_&ts ;
  set sashelp.class;
run;

Re: How to timestamp a tablename with proc sql in the format of YYYYMMDDHHSS

Tom — Thu, 21 Mar 2024 15:43:58 GMT

The macro processor does not process string literals that are bounded by single quotes.

Use double quotes instead if you want the macro processor to act on the value of the string.

Re: How to timestamp a tablename with proc sql in the format of YYYYMMDDHHSS

aarnold18 — Thu, 21 Mar 2024 15:50:37 GMT

Thanks, changing the single quotes to double got the file to export with the macro properly running.

However, it isn't in the CSV format now. it just exports as "file" Any ideas?

proc export data=Member
outfile="......../Member_&date"
dbms=csv
replace;
run;

Re: How to timestamp a tablename with proc sql in the format of YYYYMMDDHHSS

Tom — Thu, 21 Mar 2024 15:58:53 GMT

@aarnold18 wrote:

Thanks, changing the single quotes to double got the file to export with the macro properly running.

However, it isn't in the CSV format now. it just exports as "file" Any ideas?

proc export data=Member
outfile="......../Member_&date"
dbms=csv
replace;
run;

Huh? If you wrote a CSV file it IS a CSV file. Just look at it with any text editor.

I suspect you are talking about the fact that you did not NAME the file with an extension of .csv.

outfile="......../Member_&date..csv"

Note the extra period. The first one will be used by the macro processor to indicate where the name of the macro variable ends so you need the second one so it will be part of the actual name.

PS Don't let EXCEL open your CSV without double checking that it hasn't converted character strings to numbers (or worse thought they were date strings) and changed the values.