1) So the first query gets a list of SSN? Which variable contains the SSN? Because the variable you all calling SSN09 is set to NULL on every record. Also what type of variable is SSN09 supposed to be? Normally you would store SSN in a character variable, but your Oracle query is not setting a type for the variable and so I am not sure what type SAS will create for it when you pull it into SAS. Will it be a number or a character string?
Note no need for a separate PROC SORT step as you can add an ORDER BY clause to your SQL query.
CREATE TABLE inst_list AS
SELECT * FROM CONNECTION TO myconn
(SELECT DISTINCT
institution_code AS inst_code
, NULL AS ssn09
, 'A' AS record_type
FROM renaeb.ssn_update
WHERE (COMMENTS <> 'CORRECTED' OR COMMENTS IS NULL)
AND NEW_SSN IS NOT NULL
)
order by inst_code, ssn09, record_type
;
Perhaps you meant the second query?
That one appears to be creating two variables with names that look like they should contain SSN values, SSN09 and NEW_SSN.
CREATE TABLE ssn_updates AS
SELECT * FROM CONNECTION TO myconn
(SELECT
institution_code AS inst_code
, original_ssn AS ssn09
, new_ssn
, last_name
, first_name
, middle_name
, birthdate
, gender
, 'B' AS record_type
FROM renaeb.ssn_update
WHERE (COMMENTS <> 'CORRECTED' OR COMMENTS IS NULL)
AND NEW_SSN IS NOT NULL
)
order by inst_code, ssn09, record_type
;
(2)
What do you mean by PROCESS? What are you trying to do? Are you just changing SSN's and nothing else?
How do you know what semesters? Is that what the DO Loops in the data step are trying to do? Or is that information that you could have pulled from your Oracle table?
Your DATA steps looks like it is going to read in multiple text files by building the filename up from a number of other fields.
in_file_name=
catx('/'
,"&parent_path_I"
,catt('UDS',record_S,acad_year_2)
,sem
,catt('I',inst_code,'_S')
);
Your process should be (1) Generate list of files to read. (2) Read the files into SAS dataset. (3) apply updates (4) write updated files.
Is there any reason to try to combine those four steps into one step?
Perhaps you meant to do something like this, assuming that the included file just contains a subset of a data step to read the data from a file. So just an INPUT statement. With perhaps some LENGTH, LABEL, FORMAT and/or INFORMAT statements as needed to define the variables.
data file_list ;
set inst_list ;
length yr 8 sem $6 year $8 acad_year_2 $4 in_file_name $200 ;
do yr = 2010 to 2018 ;
do sem= 'SUM', 'FALL', 'SPRING';
acad_year_2 = cats(SUBSTR(put(yr,4.),3) , substr(put(yr+1,4.),3));
record_S = 'S';
in_file_name = catx('/', "&parent_path_I" , catt('UDS', record_S, acad_year_2), sem, catt('I', inst, '_S'));
output;
end;
end;
run;
data current ;
set file_list ;
INFILE IN FILEVAR = in_file_name LRECL = 250 truncover end=eof;
do while (not eof);
%INCLUDE '/uds/sas/copylib/UDS_RECORD_S_LAYOUT';
output;
end;
run;
Then update the data.
proc sort data=current;
by inst_code ssn09 ;
run;
data new ;
merge current (in=in1) ssn_updates(in=in2);
by inst_code ssn09 ;
if in2 then ssn09=new_ssn;
run;
Now if you want to re-create the text files then add a step that does the reverse of the step that read the files.
Click image to register for webinar
