topic Re: Read Access Varible into SAS in SAS Procedures

Read Access Varible into SAS

JaneLi — Tue, 24 Feb 2015 21:16:22 GMT

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;

Re: Read Access Varible into SAS

Reeza — Tue, 24 Feb 2015 23:42:29 GMT

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;

Re: Read Access Varible into SAS

JaneLi — Tue, 24 Feb 2015 23:47:14 GMT

thie is what my database looks like

Re: Read Access Varible into SAS

JaneLi — Tue, 24 Feb 2015 23:48:58 GMT

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.

Re: Read Access Varible into SAS

JaneLi — Tue, 24 Feb 2015 23:51:02 GMT

my friend said I should use"if" and "substr" to delete the junk value. but I still couldn't figure it out.

Re: Read Access Varible into SAS

Reeza — Wed, 25 Feb 2015 02:01:14 GMT

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.