How to reference a local variable that is part of a FILE statement

[JackieO]

I have tried double quotes, single quotes and ampersand. 

I have the following local variable:

    setUpDirFilename = '/uds/data/newdata/20102011/SUM/'||setUpInstDir||'/'setUpFilename;

When I display the contents of this variable I see '/uds/data/newdata/20102011/SUM/I110/110sum1011'.  Have tried to use this variable in a FILE statement so that I can write records out to it.  No luck no matter what I have tried.  Please help.

Thank you!

JackieO

[Reeza]

How did you set up the 'local' variable. I suspect what you're trying to do here doesn't make sense from a SAS perspective. Variables only exist in a data step, so did you create a macro variable? Can you show the full code of what you did so we can better understand what you're trying to do?

[JackieO]

I have never really used macros before, but did also give it a try. I also tried to use '%let', but probably got the syntax wrong or something.
Within a data step is have created these three variables and one macro(I believe):
setUpFilename = inst_code||'sum110s.dat';
setUpInstDir = 'I'||inst_code;
setUpDirFilename = '/uds/data/newdata/20102011/SUM/'||setUpInstDir||'/'setupFilename;
%let filename = setUpDirFilename;
I have to tried to establish a FILE statement in several failing ways:
FILE "&filename";
FILE 'setUpDirFilename";
FILE "setUpDirFilename";
FILE '&setUpDirFilename';
Kind of frustrated at this point. 🙂
JackieO

[Reeza]

Ok, so variables are not available outside of the data step. 

You're mixing data step logic from a data set here and macro logic for one. Can you please post your full code within the data step? I'm assuming you're trying to create multiple filenames? Is this to read in or export files out?

There are two ways to create a filename, note you're missing the fileref, myFile.

filename myFile 'path to file';

If you can explain a bit more about what you're trying to do, I can suggest the correct approach. 

[JackieO]

Here's my code:
************************************************************************************************
* *
* PROGRAM: SOCIAL_SECURITY_NUMBER_CHANGES.sas *
* AUTHOR: JACKIE F. ORAM *
* DATE: NOVEMBER 16, 2018 *
* *
* PURPOSE: TO FIND GAYLES TABLE SSN_CROSSOVER SSNS IN OEIS.STUDENT_RECORD. *
* *
***********************************************************************************************;

OPTIONS symbolgen;

%INCLUDE '/uds/sas/copylib/orapwd.sas';

LIBNAME oeis ORACLE USER=jackie PASSWORD="&encr1" PATH=oeis_dev;

************************************************************************************************
* *
* ACCESS ORACLE TABLE jackie.social_security_corrections TO DEGERMINE INST BEING UPDATED. *
* a. institution_code *
* *
***********************************************************************************************;

PROC SQL;
CONNECT TO ORACLE (USER=jackie ORAPW="&encr1" PATH=oeis_dev);
CREATE TABLE inst_list AS
SELECT * FROM CONNECTION TO ORACLE
(SELECT DISTINCT institution_code
FROM jackie.social_security_corrections
WHERE comments <> 'CORRECTED');
DISCONNECT FROM ORACLE;
QUIT;

PROC SORT DATA = inst_list;
BY institution_code;

DATA setUpList;
START: SET inst_list END = FINAL;
BY institution_code;
FILE '/uds/tmp/jotemp60.txt' PAD LRECL = 250;
inst_code = ' ';
ssn09 = ' ';
sortcode = ' ';
inst_code = institution_code;
sortcode = 'A';
PUT @1 inst_code
@4 ssn09
@13 sortcode;
IF NOT FINAL THEN GOTO START;
STOP;

DATA inst_list2;
INFILE '/uds/tmp/jotemp60.txt' PAD LRECL = 250;
INPUT inst_code $ 1-3
ssn09 $ 4-12
sortcode $ 13;

PROC SORT DATA = inst_list2;
BY inst_code ssn09 sortcode;

************************************************************************************************
* *
* ACCESS ORACLE TABLE jackie.social_security_corrections. *
* a. original_ssn *
* b. institution_code *
* c. last_name *
* d. first_name *
* e. middle_name *
* f. birthdate *
* g. gender *
* h. new_ssn *
* i. modified_by *
* j. datetime *
* *
***********************************************************************************************;

PROC SQL;
CONNECT TO ORACLE (USER=jackie ORAPW="&encr1" PATH=oeis_dev);
CREATE TABLE ssn_updates AS
SELECT * FROM CONNECTION TO ORACLE
(SELECT institution_code AS inst_code,
original_ssn AS ssn09,
new_ssn,
last_name,
first_name,
middle_name,
birthdate,
gender,
'B' AS sortcode
FROM jackie.social_security_corrections);
DISCONNECT FROM ORACLE;
QUIT;

PROC SORT DATA = ssn_updates;
BY inst_code ssn09 sortcode;

************************************************************************************************
* *
* ACADEMIC_YEAR = '20102011' *
* SEMESTER = 'SUMMER' *
* *
***********************************************************************************************;

************************************************************************************************
* *
* APPEND CURRENT "I" FILES FOR DEMOGRAPHIC RECORD S. *
* *
***********************************************************************************************;

DATA READ_S1_1011;
FILENAME S1_1011 ('/uds/data/UDSS1011/SUM/I110_S'
'/uds/data/UDSS1011/SUM/I111_S'
'/uds/data/UDSS1011/SUM/I120_S'
'/uds/data/UDSS1011/SUM/I121_S'
'/uds/data/UDSS1011/SUM/I122_S'
'/uds/data/UDSS1011/SUM/I123_S'
'/uds/data/UDSS1011/SUM/I124_S'
'/uds/data/UDSS1011/SUM/I125_S'
'/uds/data/UDSS1011/SUM/I130_S'
'/uds/data/UDSS1011/SUM/I131_S'
'/uds/data/UDSS1011/SUM/I132_S'
'/uds/data/UDSS1011/SUM/I133_S'
'/uds/data/UDSS1011/SUM/I140_S'
'/uds/data/UDSS1011/SUM/I141_S'
'/uds/data/UDSS1011/SUM/I142_S'
'/uds/data/UDSS1011/SUM/I143_S'
'/uds/data/UDSS1011/SUM/I144_S'
'/uds/data/UDSS1011/SUM/I145_S'
'/uds/data/UDSS1011/SUM/I146_S'
'/uds/data/UDSS1011/SUM/I150_S'
'/uds/data/UDSS1011/SUM/I151_S'
'/uds/data/UDSS1011/SUM/I160_S'
'/uds/data/UDSS1011/SUM/I161_S'
'/uds/data/UDSS1011/SUM/I162_S'
'/uds/data/UDSS1011/SUM/I163_S'
'/uds/data/UDSS1011/SUM/I240_S'
'/uds/data/UDSS1011/SUM/I241_S'
'/uds/data/UDSS1011/SUM/I242_S'
'/uds/data/UDSS1011/SUM/I244_S'
'/uds/data/UDSS1011/SUM/I245_S'
'/uds/data/UDSS1011/SUM/I246_S'
'/uds/data/UDSS1011/SUM/I310_S'
'/uds/data/UDSS1011/SUM/I321_S'
'/uds/data/UDSS1011/SUM/I330_S'
'/uds/data/UDSS1011/SUM/I332_S'
'/uds/data/UDSS1011/SUM/I337_S');
INFILE S1_1011 PAD LRECL = 250;
%INCLUDE '/uds/sas/copylib/UDS_RECORD_S_LAYOUT_OLD';
sortcode = ' ';
sortcode = 'C';

PROC SORT DATA = READ_S1_1011;
BY inst_code ssn09 sortcode;

DATA CHANGE_S1_1011;
blanks25 = ' ';
blanks28 = ' ';
blanks63 = ' ';
process_inst = ' ';
foundChange = ' ';
setUpFilename = blanks28;
setUpInstDir = ' ';
setUpDirFilename = blanks63;
START: SET inst_list2 ssn_updates READ_S1_1011 END = FINAL;
BY inst_code ssn09 sortcode;
IF FIRST.inst_code THEN DO;
process_inst = 'N';
foundChange = 'N';
IF sortcode = 'A' THEN DO;
process_inst = 'Y';
setUpFilename = inst_code||'sum1011s_ssn_changes.dat';
setUpInstDir = 'I'||inst_code;
setUpDirFilename = '/uds/data/newdata/20102011/SUM/'||setUpInstDir||'/'||setUpFilename;
%let fileName = setUpDirFilename;
END;
END;
IF FIRST.ssn09 THEN DO;
foundChange = 'N';
hold_new_ssn = ' ';
T_last_name = blanks25;
T_first_name = blanks25;
T_middle_name = blanks25;
T_birthdate = ' ';
T_gender = ' ';
IF sortcode = 'B' THEN DO;
foundChange = 'Y';
T_last_name = last_name;
T_first_name = first_name;
T_middle_name = middle_name;
T_birthdate = birthdate;
T_gender = gender;
IF new_ssn > '' THEN hold_new_ssn = new_ssn;
END;
END;
IF process_inst = 'Y' THEN DO;
IF sortcode = 'C' THEN DO;
IF foundChange = 'Y' THEN DO;
FILE '/uds/tmp/jotemp26.txt' PAD LRECL = 80;
PUT @1 academic_year_8
@10 inst_code
@14 ssn09
@24 hold_new_ssn
@34 last_name;
ssn09 = hold_new_ssn;
END;
FILE "&fileName" PAD LRECL = 250;
%INCLUDE '/uds/sas/copylib/PUT_UDS_RECORD_S_LAYOUT';
END;
END;
IF NOT FINAL THEN GOTO START;
STOP;

Thank you for all your assistance.
JackieO

[Astounding]

Just based on your shorter post, not on the really long one ...

You sort of created a macro variable.  It's name is FILENAME and its value is setUpDirFilename.

The %LET statement is not part of the DATA step, and so the value assigned is the actual set of characters "setUpDirFilename".

In a DATA step, if you want to transfer the value of the DATA step variable setUpDirFilename to the macro variable FILENAME, use a different tool:

call symputx('filename', setUpDirFilename);

When referring to &filename after that DATA step, do not enclose it in single quotes.  If you need quotes, use double quotes.

One of the examples you have shown is correct:

file "&filename";

[JackieO]

I am so at a loss here. I think you are saying a macro is a global variable and it must be defined outside of a DATA step. So I have coded a %let FILENAME = setUpDirFilename just before the
DATA step. You said to use a SYMPUT('FILENAME', setUpDirFilename) inside of this DATA step. This DATA step cycles through data from 46 institutions and makes corrections to SSNs.
I need one file written for the institutions that require SSN corrections. I originally set up all 46 external files, but many were zero records. So I decided to just structure a file name by the institution
requiring corrections. I believe you said that I couldn't write to the "structured" filename within the DATA step. I don't know how else to get this accomplished. Sorry to be such a bad "new user",
but I've never needed to use a macro. Been coding in SAS for 30 plus years. Will you please explain where I am going astray?
Thank you for all your assistance,
JackieO

[Reeza]

Whatever approach you're trying to take here is the problem. If you tell us in words what you're trying to do we can help. That would be saying something like: I need to read in x text file so I'm trying to automatically create the file names and then read them in.

Because the code is wrong and I don't know what you're trying to do, it's really hard to make a suggestion on what you should do.

[JackieO]

OK. This is my problem:

* We have student data directories structured by academic year, semester and institution code.

* We also have an SSN correction table that has bad SSN, institution code, last name, first name, middle name, birth date and gender data.

* I need to change bad SSN reported between and including 20102011 to present for 46 college campuses.

* The corrected data files (by academic year, semester and institution code) must be written out following our directory structure.

* These files will be processed against our edit application and distributed to various Oracle tables via our ETL process.
This particular project has never been approached in the 30 plus years that I have worked here. I would truly appreciate your expertise. We are
on a Unix platform. We currently have SAS94. If I still have not provided you with enough information, I don't know what else I can say.
JackieO

[Reeza]

Which step above involves the macro variables that you were trying to fix and work with.Is that the part about writing out the files to the correct directory structure? I'm assuming your process is done up to there?

In general though, I would break this process down further (for myself) and then solve each at a time individually.

[JackieO]

Yes. Writing out the files. I initially had a LINK WRITE_S statement that wrote out the external files with an IF ELSE IF for all the institutions, but this generated files with zero records. Are you saying to process
the corrections one institution at a time?
JackieO

[Reeza]

Not quite sure what 'process the corrections one institution at at time' means.

Do you have the data all in one file? And you want to export that to multiple text files for processing in another system, so it needs to have a specific file structure?

Is the above correct?

If so, that is something that can be easily automated.

Here's the documentation reference: https://documentation.sas.com/?docsetId=lestmtsref&docsetTarget=n15o12lpyoe4gfn1y1vcp6xs6966.htm&doc...

I'll mock up a better example after lunch, if you say this is what you're trying to do.

[JackieO]

Hi Reeza!
I append all the master files for a given academic year, semester and record format (we have S, E, M, D , L and P). So the beginning of the code that I sent to you was appending
files from 20102011 SUMMER RECORD S. This is how our edit application processes multiple record formats together. The SSN correction table contains multiple
institutions. I've tried to "flag" the institutions in the correction table and sort them in with the other two files for the DATA step. The written external files
should be "driven" by the institution flag. The output files should be written out to a similar directory structure so that the original master files are kept safe. Long story short...
you are correct.
I looked at Example 5. So this is done outside of a DATA step? I've never done that. I'm really a dinosaur I guess, but I am eager to learn new things. I will play around
with this and do some Google searching. Please don't give up on me. 🙂
JackieO

[Reeza]

Ok. Here's an example of writing records to a data set within a data step that creates CSV files.

This writes a single record to each file with the folder having the persons name and the file also having the person's name.

%let parent_path = C:\_localdata\temp;

data demo;

*data set to be exported;
set sashelp.class;

*first create directory for folder, assume it doesn't exist;
rc = dcreate(name, "&parent_path");

*create path to file with file name;
out_file_name = catx('\', "&parent_path", name, catt(name, '.csv'));

*file declaration, note fileVAR uses variable created in the data step;
file out filevar=out_file_name dlm=',';

*write header row;
put 'name, age, sex, height, weight';

*write detail row;
put name age sex height weight;

run;

And here's a second, more realistic scenario - create a file for each Sex individually in a folder called M/F with teh file called M/F respectively. 

%let parent_path = C:\_localdata\temp;

proc sort data=sashelp.class out=class;
by sex;
run;

data demo;

*data set to be exported;
set class;
by sex;

*first create directory for folder, assume it doesn't exist;
rc = dcreate(sex, "&parent_path");

*create path to file with file name;
out_file_name = catx('\', "&parent_path", sex, catt(sex, '.csv'));

*file declaration, note fileVAR uses variable created in the data step;
file out filevar=out_file_name dlm=',';

*write header row;
if first.sex then 
	put 'name, age, sex, height, weight';

*write detail row;
put name age sex height weight;

run;

I hope that helps clarify what you need to do. Note that in both of these I create a data set, called demo. You can change it to a data _null_ step, once you have it working but I like to leave it as is when testing. 

---

@JackieO wrote:
Hi Reeza!
I append all the master files for a given academic year, semester and record format (we have S, E, M, D , L and P). So the beginning of the code that I sent to you was appending
files from 20102011 SUMMER RECORD S. This is how our edit application processes multiple record formats together. The SSN correction table contains multiple
institutions. I've tried to "flag" the institutions in the correction table and sort them in with the other two files for the DATA step. The written external files
should be "driven" by the institution flag. The output files should be written out to a similar directory structure so that the original master files are kept safe. Long story short...
you are correct.
I looked at Example 5. So this is done outside of a DATA step? I've never done that. I'm really a dinosaur I guess, but I am eager to learn new things. I will play around
with this and do some Google searching. Please don't give up on me. 🙂
JackieO

---

[JackieO]

Thank you Reeza!
JackieO