Hi SAS Community,
I am importing pipe delimited .txt and .csv files into SAS and want to run a series of checks:
1. Count the number of records
2. Count the number of distinct records for ID
3. Whether the variables match a list of set variables
4. Are the values expected or in the correct format
Is there a way to output 1 dataset with all this information? As it currently stands,I produce these results into a text file that are the results of multiple proc frequencies and proc sql.
These are my expected variables:
data VARS;
input vars $;
datalines;
ID
BDAY
GENDER
ADDRESS1
ADDRESS2
CITY
STATE
ZIP
ZIP4
COUNTY_CODE
;
run;
This is my sample data:
data sample;
infile datalines dlm="|" missover dsd;
input ID $2 BDAY $10 GENDER $ ADDRESS1 $ ADDRESS2 $10. CITY $ STATE $ ZIP $ ZIP4 $ COUNTY_CODE $;
datalines;
A1|20200420|M|123 Main St.|Suite 201|Juneau|AK|99802||02112
B2|4/20/2020|Male|124 Main St.||Juneau|AK|99802|Juneau
C3|4/20/2020|M|125 Main St.||Juneau|AK|99802||02112
4-1|20200420|Male|126 Main St.|Suite 101|Juneau|AK|99802||Juneau
;
run;
Conditions:
ID:should be alpha numeric and 2 characters
BDAY: should be YYYYMMDD format
Gender: should be 1 characters
Address1 and Address 2 should be character
City should character
State should be 2 characters
zip should be 5 characters
zip4 should be no more than 4 characters
county should be the fips code which is 5 characters
Results:
Line 1: accurate and meets all criteria
Line 2: dob is not in correct format, gender is not 2 characters, does not have zip4 variable, county does not have 5 character
Line 3: dob is not in correct format
Line 4: id is not alpha numeric with no special characters, and gender is not 2 characters
So far I have:
/*Variables match list? */
proc sql;
create table plan_vars as
select strip(upcase(name)) as vars
from sashelp.vcolumn
where libname='WORK' and memname =SAMPLE;
quit;
proc sql;
create table comparevar_&file. as
select a.*, b.*,
case when a.vars = b.varsm then 'Match'
else 'No' end as var_match
from VARS as a
full join plan_vars(rename=(vars=varsm)) as b
on a.vars=b.varsm;
quit;
/*output records */
ods rtf file="&output.\FileReview.rtf";
ods noptitle;
options nodate nonumber;
proc sql;
create table ctrltot_&file. as
select count(*) as Total_number_of_records,
count(distinct ID) as Total_number_of_unique_ID,
count (ID) as tot_ID
from sample;
quit;
proc freq data = sample;
tables _all_;
format _numeric_ _character_ $miss.;
run;
proc freq data = ctrltot_&file.;
tables _all_;
run;
proc freq data=comparevar_&file.;
tables _all_;
where var_match = 'No';
run;
