BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
leonzheng
Obsidian | Level 7

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,

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

4 REPLIES 4
leonzheng
Obsidian | Level 7

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

Reeza
Super User

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

Reeza
Super User

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,


 

 

leonzheng
Obsidian | Level 7

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.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 1110 views
  • 0 likes
  • 2 in conversation