topic Re: how to do a new select by list in a existing dataset in SAS Programming

how to do a new select by list in a existing dataset

leonzheng — Fri, 05 Jan 2018 23:39:27 GMT

I want to pull out data by SAS from database.

Suppose I have a database with thousands of people's information. I made a filter to randomly select 2 people born from each month of certain year. dataset LIST looks like below:

name birthday month

green xxxx 1

white xxxx 2

brown xxxx 3

black xxxx 4

and so on

Now I want to use the name as one criterion for another data pulling.

My code will be:

proc sql;

connect to xxxx(database);

create table want

as select * from connection to database

(select a.name, a.occupation, a.employer....

from surveydet.list

where (a.occupation in xxxxxxxxxx)

and (a.employer in xxxxxxxxxxx)

AND (A.NAME= LIST.NAME);

disconnect from databse;

quit;

The code seems not work, please help to teach me how to pull data using names exactly pre-selected in dataset LIST.

Thanks,

Re: how to do a new select by list in a existing dataset

leonzheng — Fri, 05 Jan 2018 23:40:20 GMT

sorry, in the example, I only list 1 person for each month.

Re: how to do a new select by list in a existing dataset

Reeza — Fri, 05 Jan 2018 23:44:08 GMT

Given what you've shown here, you can't do that because you're using pass through SQL not PROC SQL. This means you're passing the query to the DB so all the data has to be on the data base, including your LIST table.

If it is you can just do:

where x in (Select name from list) and occupation in (select .... )

It's not quite clear if you want to do it for each name, or for all names at once.

Assuming all names at once you can create a macro variable with the list of names, if it's less than 64K characters in total.

Then it becomes:

where x in (&name_list)

I think you'll need to post some more details, your question as it stands is quite vague so it's hard to give specific solutions.

If this is related to your query from yesterday then CALL EXECUTE is still the best method, IMO.

@leonzheng wrote:

I want to pull out data by SAS from database.

Suppose I have a database with thousands of people's information. I made a filter to randomly select 2 people born from each month of certain year. dataset LIST looks like below:

name birthday month

green xxxx 1

white xxxx 2

brown xxxx 3

black xxxx 4

and so on

Now I want to use the name as one criterion for another data pulling.

My code will be:

proc sql;

connect to xxxx(database);

create table want

as select * from connection to database

(select a.name, a.occupation, a.employer....

from surveydet.list

where (a.occupation in xxxxxxxxxx)

and (a.employer in xxxxxxxxxxx)

AND (A.NAME= LIST.NAME);

disconnect from databse;

quit;

The code seems not work, please help to teach me how to pull data using names exactly pre-selected in dataset LIST.

Thanks,

Re: how to do a new select by list in a existing dataset

Reeza — Fri, 05 Jan 2018 23:46:16 GMT

Then wrapping it in a macro and using CALL EXECUTE is your easiest choice.

Re: how to do a new select by list in a existing dataset

leonzheng — Fri, 05 Jan 2018 23:57:54 GMT

Thank you, my real case is much more complicated to describe and contain company information. So I tried to use simple examples to describe my question. I think your suggestion is good. I will try to form a list file and get selection information from it with macro.