topic Re: Creating Export Name from Variables? in SAS Programming

Creating Export Name from Variables?

shellp55 — Mon, 09 Nov 2020 17:35:28 GMT

Hello

I currently set the value of a file name as %let export_db="E:\data\2020 21\Q2\inst5555.xlsx";

I can set the institution variable as %let export_db="E:\data\2020 21\Q2\inst_&dqInst..xlsx" (where dqinst is set separately) and it works. But I have many different institutions at various periods in their data and I would like to create variables for fiscal year and quarter so I don't have to change all the time but how do I put it together? For example, below doesn't work:

"E:\data\"||&fiscal||"\"||&qtr||"\"||"DAD DQ_&DQInst..xlsx"

Any and all help greatly appreciated.

Re: Creating Export Name from Variables?

Reeza — Mon, 09 Nov 2020 17:47:50 GMT

"E:\data\&fiscal.\&qtr.\DAD DQ_&DQInst..xlsx"

Expand on your original solution - no need to concatenate manually.

Re: Creating Export Name from Variables?

PaigeMiller — Mon, 09 Nov 2020 17:49:30 GMT

You don't need || to concatenate macro variables and text. Try this:

"E:\data\&fiscal\&qtr\DAD DQ_&DQInst..xlsx"

Re: Creating Export Name from Variables?

shellp55 — Mon, 09 Nov 2020 18:19:26 GMT

Thank you for your responses. Previously, when I wasn't "building from variables", the Excel file would just open up with the name I had created i.e. DAD_DQ_Inst5555.xlsx. But now that I am building the file name from variables I get an error message that "physical file does not exist". Why would that be and how do I correct it? Thanks.

Re: Creating Export Name from Variables?

Reeza — Mon, 09 Nov 2020 18:31:50 GMT

Double check how the full file path is resolving. I'm guessing it's adding some additional spaces somewhere so the file path doesn't end up matching exactly.

Run the code with the MPRINT option on and show us the actual error if you cannot resolve it.

Re: Creating Export Name from Variables?

shellp55 — Mon, 09 Nov 2020 18:34:38 GMT

You are correct, Reeza, I was missing a subfolder in the name. Now it works and all is well. Thanks very much.