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

[JackieO]

So close and yet so far...

Please review my modified code below:

************************************************************************************************
* *
* *
* 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;

%let parent_path = /uds/data/newdata/20102011/SUM/I;

DATA CHANGE_S1_1011;
blanks25 = ' ';
blanks65 = ' ';
process_inst = 'N';
foundChange = 'N';
fileInst = ' ';
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';
fileInst = inst_code;
rc = dcreate(fileInst, "&parent_path");
out_file_name = catx('/', "&parent_path", fileInst, catt(fileInst, 'sum1011s_ssn_changes.dat'));
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 ssn09 = hold_new_ssn;
FILE OUT FILEVAR = out_file_name dlm=',';
END;
END;
IF NOT FINAL THEN GOTO START;
STOP;

*.log file shows created external file as the following:

ERROR: Physical file does not exist, /uds/data/newdata/20102011/SUM/I/110/110sum1011s_ssn_changes.dat.

Should be /uds/data/newdata/20102011/SUM/I110/110sum1011s_ssn_changes.dat.

Hope you can see where I went wrong.

Thank you, Reeza!

JackieO

[Reeza]

Separate your code please. Which is the step that's generating the error, I don't want to see all the code. Why do you have all those filename statements? Is that for writing out the files? With the process I'm suggesting that isn't required.

[JackieO]

Sorry.
%let parent_path = /uds/data/newdata/20102011/SUM/I;

DATA CHANGE_S1_1011;
blanks25 = ' ';
blanks65 = ' ';
process_inst = 'N';
foundChange = 'N';
fileInst = ' ';
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';
fileInst = inst_code;
rc = dcreate(fileInst, "&parent_path");
out_file_name = catx('/', "&parent_path", fileInst, catt(fileInst, 'sum1011s_ssn_changes.dat'));
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 ssn09 = hold_new_ssn;
FILE OUT FILEVAR = out_file_name dlm=',';
END;
END;
IF NOT FINAL THEN GOTO START;
STOP;
JO

[Reeza]

Why do you have START/GOTO in here? What are you using that for? Just want to make sure I understand it correctly?

[JackieO]

It is looping through the input data. I know it is old school and probably not still done today.
JackieO

[Reeza]

In that step, you're both still processing data and trying to export. Can you separate out those steps? Process your data first and ensure it's correct then do the export. I would personally recommend removing the Start/Stop sections unless you explicitly need them and understand how it's working. I don't know how it's working since a data step loops by default.

[JackieO]

Reeza,
I am sorry to be so irritating to you. I don't understand your method either. Guess I will keep trying to make that one line work that contains
the catt and catx functions. I guess they are functions. New to me, too. Anyway...I really do appreciate all your help.
Have a better day and thank you,
JackieO

[Reeza]

See my comments below:

%let parent_path = /uds/data/newdata/20102011/SUM/I;

DATA CHANGE_S1_1011;
	blanks25=' ';
	blanks65=' ';
	process_inst='N';
	foundChange='N';
	fileInst=' ';
START: *this seems problematic;

*you are working with three input files here - appended, which interleaves them. This is what I call processing;
	SET inst_list2 ssn_updates READ_S1_1011 END=FINAL;
	BY inst_code ssn09 sortcode;

*this seems to be doing the file name for each institution;
	IF FIRST.inst_code THEN
		DO;
			process_inst='N';
			foundChange='N';

			IF sortcode='A' THEN
				DO;
					process_inst='Y';
					fileInst=inst_code;
					rc=dcreate(fileInst, "&parent_path");
					out_file_name=catx('/', "&parent_path", fileInst, catt(fileInst, 
						'sum1011s_ssn_changes.dat'));
				END;
		END;
*not sure what this is doing, but doesn't seem to relate to outputting data;
*you may want to look into call missing as well;
	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
						ssn09=hold_new_ssn;
*only place where the file is written out - but no put statement so no records are output anywhere anyways;
					FILE OUT FILEVAR=out_file_name dlm=',';
				END;
		END;
*loops by default, you shouldn't need this these days;
	IF NOT FINAL THEN
		GOTO START;
	STOP;

[JackieO]

Reeza!
It works! It works! Thank you again for EVERYTHING!!!
SEE...

NOTE: The file OUT is:
Filename=/uds/data/newdata/20102011/SUM/I110/110sum1011s_ssn_changes.dat,
Owner Name=jackie,Group Name=uds,
Access Permission=-rw-rw----,
Last Modified=05Dec2018:15:56:08

NOTE: The file OUT is:
Filename=/uds/data/newdata/20102011/SUM/I146/146sum1011s_ssn_changes.dat,
Owner Name=jackie,Group Name=uds,
Access Permission=-rw-rw----,
Last Modified=05Dec2018:15:56:08

NOTE: 9492 records were written to the file OUT.
The minimum record length was 222.
The maximum record length was 222.
NOTE: 10279 records were written to the file OUT.
The minimum record length was 222.
The maximum record length was 222.
Have a great evening!!!!!
JackieO

[Reeza]

A days pay well earned! Cheers!