DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

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

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,


Accepted Solutions
Solution
‎01-05-2018 06:58 PM
Super User
Posts: 22,857

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

Posted in reply to leonzheng

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,


 

 

View solution in original post


All Replies
Contributor
Posts: 23

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

Posted in reply to leonzheng

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

Super User
Posts: 22,857

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

Posted in reply to leonzheng

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

Solution
‎01-05-2018 06:58 PM
Super User
Posts: 22,857

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

Posted in reply to leonzheng

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,


 

 

Contributor
Posts: 23

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

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 195 views
  • 0 likes
  • 2 in conversation