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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.