I am trying to read two variables from my access database and then do SQL. But there are three variable in my database: personID, country and city. I only need to read two variables personID and city. How SAS only read the first and third column?
data PersonDS3 ;
Infile Person3 missover firstobs=2;
Input PersonID City & $25. ;
Proc SQL;
Create table Most_PersonID AS
SELECT city Count(PersonID)AS CountOfPersonID
FROM PersonDS3
GROUP BY city;
quit;
Proc sort data=Most_PersonID; by Descending CountOfPersonID;
Why are you using infile to read from an Access DB?
I would assume you're using ODBC or some other method that would allow you to DROP the variable on the set statement.
data personD3;
set accessDB (drop=unwanted_var);
run;
thie is what my database looks like
And I only want to read PersonID, country and city. But in the city column there are always some gender information. I don't know how to deal with these junk values.
my friend said I should use"if" and "substr" to delete the junk value. but I still couldn't figure it out.
Were you provided this Access DB in this format or did you receive a text file?
I'd go back a step and fix it there first.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.