BookmarkSubscribeRSS Feed
JaneLi
Calcite | Level 5

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;

5 REPLIES 5
Reeza
Super User

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;

JaneLi
Calcite | Level 5

thie is what my database looks like

1.PNG

JaneLi
Calcite | Level 5

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.22.PNG

JaneLi
Calcite | Level 5

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

Reeza
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 867 views
  • 0 likes
  • 2 in conversation